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…
In your case you don’t have a source table, so you may have to use SYSIBM.SYSDUMMY1 for this. Note that the mergeWhen and mergeWhenNotMatched clauses do not need use the src table… > On 29. nov. 2014, at 13.25, John English <[email protected]> wrote: > > Something that I find crops up quite often is code to deal with duplicate > keys. I often want to insert into a table, or update if the key already > exists. In MySQL I can just use INSERT ... ON DUPLICATE KEY UPDATE ... for > this, but with Derby I end up with code that looks like this: > > try { > //... insert new row > } > catch (SQLException e) { > if (e.getSQLState().equals(DUPLICATE_KEY)) { > // ... update existing row > } > else { > throw e; > } > } > > In the absence of something like INSERT ... ON DUPLICATE KEY UPDATE, would it > not perhaps be a good idea for Derby to subclass SQLException so that it > could throw a (say) SQLKeyExistsException to avoid ugly repetitive code like > the above? Or is there already something that I've overlooked that addresses > this problem? > > TIA, > -- > John English
