[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