[thelist] sql update statement using a subquery (sql server)
Phil Turmel
pturmel-webdev at turmel.org
Mon May 28 18:05:26 CDT 2007
Sarah Adams wrote:
> 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.
>
> Ideas?
Have you tried any of the following? (My SQL server isn't handy, so I can't test for you.)
UPDATE r
SET ReservedBy = r2.ReservedBy
FROM Reservations r, Reservations r2
WHERE r.ReservedBy IS NULL
AND r.ParentReservationID = r2.ReservationID
UPDATE Reservations
SET r.ReservedBy = r2.ReservedBy
FROM Reservations r, Reservations r2
WHERE r.ReservedBy IS NULL
AND r.ParentReservationID = r2.ReservationID
UPDATE r
SET r.ReservedBy = r2.ReservedBy
FROM Reservations r, Reservations r2
WHERE r.ReservedBy IS NULL
AND r.ParentReservationID = r2.ReservationID
HTH,
Phil
More information about the thelist
mailing list