Lets say we have a table like this:
And we insert some records into it:
And now we want to do:
I was expecting that this would update the c4 column to 'zzz' in the row where c1 = 2014001. However this is what happens:
I couldn't find anything in the help about this combination. Is this a bug or is this limitation missing in the documentation?
Tested with version 22.214.171.1248
asked 05 Dec '14, 09:21
The underlying issue here is that the COMPUTE expression values are not computed until AFTER the insert has occurred. I.e. the server first looks at each row using the original supplied values and makes a determination on whether there is going to be a collision, and if not then it does the insert. After the insert the compute clause is triggered and the value of the column is computed. In your example the computed values are causing a PK violation and hence the error that is raised.
Volker's answer using the MERGE statement is a nice solution to what you are trying to do.
answered 07 Jan '15, 09:23
I haven't visited the MERGE potentional here but this is what I can see about this.
Columns c2 and c3 are not the primary key and not a uniqueness constraint so your insert-on-existing-update operation is equivalent of these two statements
both of which will throw the same exception. The update takes that 'equivalent' form since there is nothing special about either c3 or c4 and so must be included into the SET-CLAUSE.
You can see that from adding those 2 columns as a table uniqueness constraint as follows:
and now the parser knows there is something special about c2 and c3 when it forms the update operation.
While not much of a workaround, hopefully that explains the behavior more clearly.
answered 02 Jan '15, 12:02
Nick Elson S...
In contrast to INSERT ON EXISTING, the MERGE statement allow you to specify the match condition (as discussed here) so you can define it based on the compute expression.
The following statements (based on your sample and the two initial INSERTs) will do both an update and an insert:
merge into dba.t1 (c2, c3, c4) using (select 2014, 1, 'zzz') as s1 (c2, c3, c4) on t1.c2 = s1.c2 and t1.c3 = s1.c3 when not matched then insert when matched then update; merge into dba.t1 (c2, c3, c4) using (select 2014, 3, 'ghi') as s1 (c2, c3, c4) on t1.c2 = s1.c2 and t1.c3 = s1.c3 when not matched then insert when matched then update; select * from dba.t1 order by 1;
Here I have used an equality comparison of columns c2 and c3 but you could use the compute expression itself, too, i.e. "on t1.c1 = s1.c2 * 1000 + s1.c3".