On 01/12/2014 03:47, Rick Hillegas wrote:
On 11/30/14 4:17 AM, Dyre Tjeldvoll wrote:
Anyway here is my attempt at formulating INSERT ON DUPLICATE KEY UPDATE using
MERGE (untested, no warranty):
MERGE INTO T AS DST USING SYSIBM.SYSDUMMY1 ON DST.<keycolumn> = ?
WHEN MATCHED THEN UPDATE DST SET DST.<col> = ? …
WHEN NOT MATCHED THEN INSERT INTO DST VALUES(?,?,…,?)
Hi John,
The following script shows another example of how to use the MERGE statement.
Hope this helps,
-Rick
connect 'jdbc:derby:memory:db;create=true';
create table t( keyCol int, payloadCol int );
insert into t values ( 1, 1 ), ( 2, 2 );
merge into t dest using t src
on dest.keyCol = 3
when matched then update set payloadCol = 4
when not matched then insert values ( 3, 3 );
select * from t;
Thanks to both of you for the pointers. I'll have a play with it.
However, why not in any case consider deriving a separate subclass of
SQLException for the duplicate key case? It wouldn't break any existing code,
it's presumably easy to identify the code sites which should throw it, and it
would clarify exception handling in some cases.
--
John English
