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 >
