[thelist] sql update statement using a subquery (sql server)

Sarah Adams sarahwbs at gmail.com
Mon May 28 13:53:11 CDT 2007

I'm tearing my hair out with this. It should be pretty simple, I think,
but either it's a lot more complicated than I think or I've just been
staring at it too long and I'm not thinking right. I want to update a
table based on a related value from the same table. (Long story short
it's a very bad db design that I'm trying to fix as best I can.) Here's
the query I'm running (in SQL Server):

UPDATE Reservations
SET    ReservedBy = r2.ReservedBy
FROM   Reservations r, Reservations r2
WHERE  r.ReservedBy IS NULL
AND    r.ParentReservationID = r2.ReservationID

I get this error:
Server: Msg 8154, Level 16, State 1, Line 1
The table 'Reservations' is ambiguous.

I've tried several variations on this query, as well as using a
correlated subquery. And much googling. No luck.


More information about the thelist mailing list