[thelist] sql update statement using a subquery (sql server)
Sarah Adams
sarahwbs at gmail.com
Tue May 29 07:20:27 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.
> >
> > 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 :)
More information about the thelist
mailing list