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;
|