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

[Christie Mason]

Both table names are the same?

Christie Mason

-----Original Message-----
From: thelist-bounces at lists.evolt.org
[mailto:thelist-bounces at lists.evolt.org]On Behalf Of Sarah Adams
Sent: Monday, May 28, 2007 1:53 PM
To: thelist at lists.evolt.org
Subject: [Spam] [thelist] sql update statement using a subquery (sql
server)


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?
-- 

* * Please support the community that supports you.  * *
http://evolt.org/help_support_evolt/

For unsubscribe and other options, including the Tip Harvester 
and archives of thelist go to: http://lists.evolt.org 
Workers of the Web, evolt !