473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Select with aliases, please help

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

Aug 9 '05 #1
3 1350
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

Aug 9 '05 #2
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.

Aug 10 '05 #3
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)
Aug 10 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: steve stevo | last post by:
strSearch is a dynamically created search string Copy of select statement below sql = "SELECT Master.Master_Key,Master.First_App,Original_Info_Date,Last_Info_Date,Area.Ar ea_Name AS...
1
by: e_AK_05 | last post by:
I have a question and I can't figure out how to do it. I have a select statment : SELECT table1.* AS table1.*, table2.* AS table2.* FROM table1, table2 WHERE 1 this does not work...for me at...
17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
0
by: lukit | last post by:
UPDATE SET Stat="T",TransferedTo=b.Store FROM a WHERE stat = "PS" AND EXISTS( SELECT b.* from b WHERE a.Model=b.Model AND a.Make=b.Make AND a.Year=b.Year AND a.Trim=b.Trim AND...
0
by: Supernaut | last post by:
HI, I make one project in Visio 2003. I link a shape from Visio with table from Access Data Base. In this Access Table I have a LookUp field. And the problem is that in visio for this LookUp...
15
by: jacob navia | last post by:
Recently, we had a very heated thread about GC with the usual arguments (for, cons, etc) being exchanged. In one of those threads, we came into the realloc problem. What is the realloc...
1
by: nullGumby | last post by:
I'm trying to get a UNION of UserIDs from multiple subselects. The original query--which puts all the found UserIDs into separate columns, looks like this: ...
6
by: dom.k.black | last post by:
Is it still common practice to use type aliases (INT, PCHAR etc). It looks ugly and breaks the syntax highlighting, are there any advantages these days (MSVC++6.0 and later)? I could understand...
2
by: paulmitchell507 | last post by:
I think I am attempting a simple procedure but I just can't figure out the correct syntax. My asp (classic) page runs a SELECT query to obtain dates and ID's from 2 tables uSQL = "SELECT...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.