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

[Sarah Adams]

> > 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.)
> <snip>

Excellent! Thanks, Phil, this one worked:

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

I guess it makes sense, now that I think about it, to keep the
aliasing (if that's what it's called) in the from clause.

I think it's crazy that I've never run into something like this before
- just goes to show that you learn something new every day... even
when you're not trying :)