db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dyre Tjeldvoll <Dyre.Tjeldv...@oracle.com>
Subject Re: Duplicate key feature request
Date Sun, 30 Nov 2014 12:17:28 GMT

> 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


Mime
View raw message