"Insert on existing update" - updates pKey fields

[former_member329524]

Hello, all

I have a question: why command "insert on existing update" updates not only data fields, but the key fields as well?

Here is my use case:

The database is no case sensitive

A record is entered with alphanumeric key in lower case.

Then an external application executes command "insert on existing update" and sends the key in upper case. The record is updated and so is the key field.

This would not be a real issue, if the table was not part of an upload publication. The system notices change in the key and disallows the update.

Is there a parameter, when I can set that during "insert on existing update" only the non-pkey fields will be updated?

Thank you

Arcady

[regdomaratzki]

The behaviour you are seeing with insert on existing update is expected.  There is no option you can set to change the behaviour of the INSERT ON EXISTING COMMAND, but you could choose to use the MERGE command instead, where you have more options and flexibility.  For example :

create table t3 ( pk char(4) primary key, c1 integer );
insert into t3 values ( 'reg', 1 );
commit;

merge into t3 (pk,c1)
using with auto name 
( select 'Reg' as pk,9 as c1 ) as dt
on primary key
when matched then update set t3.c1 = dt.c1
when not matched then insert;
commit;

Reg

[former_member329524]

Merge command is too difficult to implement in various cases. Not to mention, it will probably run slower than "insert on existing update".

I would like to add a request change for future versions to include a server option not to update the primary key on "insert on existing" command. There really is no apparent reason to update the primary key field in this case.

Or, as an alternative, set this parameter automatically, when the table is a part of an upload publication. In this case, the IOEU command would not be blocked and the key will not be updated.

[regdomaratzki]

Can you describe a case where the MERGE command would be too difficult to implement?

Can you describe a scenario where the MERGE command runs slower than insert on existing update?

Reg

[former_member329524]

Regarding the complexity, I think the syntax of IOEU speaks for itself. All the user has to specify is the select statement for the source data.

Regarding the speed - it is a mere guess, but it makes sense,since the IOEU only works on primary key, which makes it correctly indexed by definition.

[regdomaratzki]

Interesting question.  I would not have expected the primary key column to be updated in this scenario, but it is.  I have e-mailed the Correct Person To Decide What The Proper Behaviour Is and will post back when here back from them.

Short answer: There is currently no way to change the current behaviour.

Reg