Select with aliases, please help

[lukit]

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

[Razvan Socol]

You probably want one of these:

UPDATE Table1 SET Stat='T', TransferedTo=(
SELECT b.Store 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'
)
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'
)

UPDATE Table1 SET Stat='T', TransferedTo=b.Store
FROM Table1 a INNER 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
WHERE a.stat = 'PS' AND b.New='N'

Razvan

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You might want to learn SQL instead of what you are writing. Strings
are always in single quotes and good programmers never use the UPDATE..
FROM proprietary syntax because of the unpredictable results. They
also do not use reserved words, or data element names that are so vague
as to be useless. Thjis is awful code even for an example.

My guess, since we have no DDL, is something like this.

UPDATE Foobar
SET stat = 'T',
Transferred_to
= (SELECT B.store
FROM Bloob AS B
WHERE stat = 'PS'
AND Foobar.model = B.model
AND Foobar.make = B.make
AND Foobar.year = B.year
AND Foobar.trim = B.trim
AND Foobar.doorsbody = B.doorsbody
AND Foobar.price = B.price
AND Foobar.color = B.color
AND Foobar.trans = B.trans
AND Foobar.cyl = B.cyl
AND Foobar.miles = B.miles
AND Foobar.store = B.store
AND B.new = 'N');

This statement will error out when Bloob has more than one qualifying
store. The UPDATE FROM syntax will give you a random result.

[Hugo Kornelis]

On 10 Aug 2005 08:12:17 -0700, --CELKO-- wrote:

(snip)

This statement will error out when Bloob has more than one qualifying
store. The UPDATE FROM syntax will give you a random result.

Hi Joe,

You forgot to tell the OP that the statement you posted will also set
the column to NULL in all rows that have no qualifying store. Something
the OP didn't want, judging by the EXISTS clause in the query he posted.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)