db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ronald Rudy <ronchal...@gmail.com>
Subject Re: Using IN clause with multiple columns
Date Wed, 21 Apr 2010 10:51:04 GMT
If that doesn't work, a very inelegant solution might be something like:

DELETE FROM FOO WHERE a || ':' || b IN (SELECT a || ':' || b FROM BAR WHERE MODIFIED = 1)

It probably won't perform well, and you'd have to convert non-varchar fields to varchars I
think.  But it would work.

Alternative, though I haven't specifically worked with them in Derby, you could create a stored
procedure that does a simple join selecting the values to be deleted and iterating over a
cursor, deleting records as you go.  

Or doing the above programmatically, batching delete statements.

-Ron

On Apr 21, 2010, at 3:55:18 AM, Knut Anders Hatlen wrote:

> On 04/21/10 12:20 AM, David Van Couvering wrote:
>> I have two tables with columns a,b that together comprise the primary
>> key.
>> 
>> In Oracle I can do something like DELETE FROM FOO WHERE  (a, b) IN
>> (SELECT a, b FROM BAR WHERE MODIFIED = 1)
>> 
>> but in Derby this gives me a syntax error saying Error: Syntax error:
>> Encountered "," at line 1, column XX.  
>> 
>> The documentation for "IN" also suggests that the subquery can only
>> return a single column.
>> 
>> How would I do this, when my tables have a multi-column primary key
>> like above?
>> 
> 
> Hi David,
> 
> Would this work?
> 
> DELETE FROM FOO WHERE EXISTS
>    (SELECT * FROM BAR WHERE MODIFIED = 1
>        AND BAR.A = FOO.A AND BAR.B = FOO.B)
> 
> 
> -- 
> Knut Anders
> 


Mime
View raw message