Update from a subquery

Hello all --

Can't figure out why this doesn't work, and/or how to make it work:

I have two tables X (columns A, B, and C), and Y (columns D, E, F, ...).
I am trying to update columns B and C with the minimum values of E and F; the join is on columns A and D.

So, I try:

update X
set (A, B, C) = (
select D, min(E), min(F)
from Y
group by D
) Y1
where X.A = Y1.D

BTW, I don't need to update the value of A, but I don't know if I can eliminate that either.

TIA Pete Received on Thu Aug 26 2004 - 00:41:14 CDT