On 12/1/14 4:08 AM, John English wrote:
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.
Hi John,
Note that the SQLException which is thrown is already a refined subclass
of SQLException. The failed insert will throw a
java.sql.SQLIntegrityConstraintViolationException. That may help you
code this approach to the problem.
Hope this helps,
-Rick
