db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Duplicate key feature request
Date Mon, 01 Dec 2014 12:08:03 GMT
On 01/12/2014 03:47, Rick Hillegas wrote:
> On 11/30/14 4:17 AM, Dyre Tjeldvoll wrote:
>> 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(?,?,…,?)
>>
> 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;

Thanks to both of you for the pointers. I'll have a play with it.

However, why not in any case consider deriving a separate subclass of 
SQLException for the duplicate key case? It wouldn't break any existing code, 
it's presumably easy to identify the code sites which should throw it, and it 
would clarify exception handling in some cases.
-- 
John English

Mime
View raw message