Select with aliases, please help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select with aliases, please help

Author

Topic

lukit
Starting Member

6 Posts

Posted - 2005-08-09 : 11:14:31

UPDATE [TABLE1] SET Stat="T",TransferedTo=b.Store
FROM [TABLE1] a
WHERE stat = "PS"
AND EXISTS(
SELECT b.* from [TABLE2] b WHERE a.Model=b.Model AND a.Make=b.Make AND a.Year=b.Year AND a.Trim=b.Trim AND a.DoorsBody=b.DoorsBody AND a.Price=b.Price AND a.Color=b.Color AND a.Trans=b.Trans AND a.Cyl=b.Cyl AND a.Miles=b.Miles AND a.Store=b.Store AND b.New="N")

I got error:

[Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'b' does not match with a table name or alias name used in the query.

Please help me, it`s all about b.Store

X002548
Not Just a Number

15586 Posts

Posted - 2005-08-09 : 11:38:38

How about


UPDATE a
   SET Stat='T',TransferedTo=b.Store 
  FROM [TABLE1] a 
  JOIN [TABLE2] b 
    ON a.Model	  = b.Model 
   AND a.Make	  = b.Make 
   AND a.Year	  = b.Year 
   AND a.Trim	  = b.Trim 
   AND a.DoorsBody= b.DoorsBody 
   AND a.Price	  = b.Price 
   AND a.Color	  = b.Color 
   AND a.Trans	  = b.Trans 
   AND a.Cyl	  = b.Cyl 
   AND a.Miles	  = b.Miles 
   AND a.Store	  = b.Store 
   AND b.New	  = 'N'
 WHERE stat 	  = 'PS'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

lukit
Starting Member

6 Posts

Posted - 2005-08-09 : 14:29:36

thanks, works perfectly :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources