db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Duplicate key feature request
Date Mon, 01 Dec 2014 01:47:47 GMT
On 11/30/14 4:17 AM, Dyre Tjeldvoll wrote:
>> On 29. nov. 2014, at 19.02, John English<john.foreign@gmail.com>  wrote:
>>
>> On 29/11/2014 14:50, Dyre Tjeldvoll wrote:
>>> I think the SQL-standard way of doing things like this is with the MERGE
>>> statement<http://db.apache.org/derby/docs/10.11/ref/rrefsqljmerge.html>
 (just
>>> added to Derby in the latest release). Though the syntax may be a bit more
>>> cumbersome…
>> A *lot* more cumbersome, if I understand it correctly!
> Arguably yes. But the MERGE statement aims to cover a number of non-std extensions like
this INSERT OR IGNORE UPDATE OR INSERT etc. With the merge statement you can also do all your
updates in a “shadow” table and then merge it with the real table later.
>
> 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(?,?,…,?)
>
>> But perhaps you could convince me otherwise by posting a snippet to show me how to
do it?
>>
>> Basically, I'd just like to be able to do this:
>>
>>   try (Transaction t = new Transaction(connection)) {
>>     //... insert new row
>>     t.commit();
>>   }
>>   catch (SQLDuplicateKeyException e) {
>>     // ... update existing row
>>     t.commit();
>>   }
>>
>> ... and let any other exceptions propagate. As it is, having to use an "if" to distinguish
between different exceptions is very ugly and non-O-O.
>>
>> -- 
>> John English
>
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;


Mime
View raw message