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: Unique constraints and nulls
Date Mon, 19 May 2014 12:22:55 GMT
On 05/17/2014 01:16 PM, John English wrote:
> On 15/05/2014 10:46, Dyre Tjeldvoll wrote:
>> Could you not do
>>
>> DELETE FROM FOO WHERE A = ? AND ( B = ? OR B IS NULL )
>
> Unfortunately not. If B is not null, it would also delete the
> corresponding A where B is null. For example:
>     A = X, B = Y
>     A = X, B = NULL
> Your solution would delete both, but I want to delete ONLY the row with
> the matching value for B.
>

I think you then can do

DELETE FROM WITH_NULLS WHERE A = ? AND
    CASE WHEN ? IS NULL THEN B IS NULL ELSE B = ? END

You then have to set the B value twice on your prepared statement, but I 
think it will work.

It will not work in IJ though, as you cannot supply NULL in the VALUES 
clause when executing the statement, which looks like a bug to me...


-- 
Regards,

Dyre

Mime
View raw message