How can I emulate UPDATE x SET (col1, col2) = ( SELECT a,b FROM y ) in Derby?

Question

Derby doesn't support this syntax:

UPDATE x SET (col1, col2, ...) = ( SELECT a,b,... FROM y ... )

(see this bug). Does anyone have a workaround other than creating a loop in Java which does the select and sends N updates back?

[EDIT] Note that I have 50 columns and a complex condition (joins with x and EXISTS and whatnot). So I'dd like to avoid repeating the SELECT 50 times, please :)

Answer 1

I'm not sure that's valid sql (I'm more a MS SQL guy so I could be way way off).

Normally I'd do:

update Orders
set Orders.Code = OtherOrders.Code,
    Orders.Name = OtherOrders.Name
from Orders inner join Orders as OtherOrders 
    on OtherOrders.OrderId = Orders.OrderId

Answer 2

I too would do it the was rbobby siad but I'm also a MSSQL person.

Have you tried:

UPDATE x 
SET (col1)= ( SELECT a FROM y where y.fkfield = x.pkidfield),
(col21)= ( SELECT b FROM y where y.fkfield = x.pkidfield), )