On 11/30/14 4:17 AM, Dyre Tjeldvoll wrote:
On 29. nov. 2014, at 19.02, John English<[email protected]> 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;
