db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@gmail.com>
Subject Re: Duplicate key feature request
Date Mon, 01 Dec 2014 22:30:05 GMT
On 12/1/14 4:08 AM, John English wrote:
> 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.
Hi John,

Note that the SQLException which is thrown is already a refined subclass 
of SQLException. The failed insert will throw a 
java.sql.SQLIntegrityConstraintViolationException. That may help you 
code this approach to the problem.

Hope this helps,
-Rick

Mime
View raw message