Update A, B set A.Y = B.X where A.ID = B.ID

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 2008 Forums

Transact-SQL (2008)

Update A, B set A.Y = B.X where A.ID = B.ID

Author

Topic

Guus2005
Starting Member

10 Posts

Posted - 2010-04-23 : 05:04:37

Why doesn't this query work?

UPDATE A, B, C, D
   Set A.W_BUSINESS_LINE_ID = B.C_BUSINESS_LINE_ID,
       A.W_COUNTRY_ID       = C.C_COUNTRY_ID             ,
       A.W_CONTINENT_ID     = D.C_CONTINENT_ID
 WHERE B.C_BS_ID  = C.C_BS_ID
   AND C.C_CS_ID  = A.C_CS_ID   
   AND D.C_GM_ID  = C.C_GM_ID;

Is there a better way to write these queries?

Thx!
Guus

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-23 : 05:11:42

The easiest answer is: you cannot update more than one table in a single statement.

The hard way: search for: "sql server 2008 update multiple tables"

That gives for example: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100207

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-23 : 06:18:59

see

http://weblogs.sqlteam.com/brettk/archive/2008/04/03/60560.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-23 : 06:26:32

your query should be


UPDATE A
   Set A.W_BUSINESS_LINE_ID = B.C_BUSINESS_LINE_ID,
       A.W_COUNTRY_ID       = C.C_COUNTRY_ID             ,
       A.W_CONTINENT_ID     = D.C_CONTINENT_ID
FROM       B 
INNER JOIN C ON B.C_BS_ID  = C.C_BS_ID
INNER JOIN A ON C.C_CS_ID  = A.C_CS_ID   
INNER JOIN D ON D.C_GM_ID  = C.C_GM_ID;

KH
[spoiler]Time is always against us[/spoiler]

Guus2005
Starting Member

10 Posts

Posted - 2010-04-23 : 07:00:52

I am trying to update one table using three others. The Jet database engine (MS Access 2003) can work with the statement i showed. I thought SQL Server used the same engine. Thank you all for the pointers and even the rewritten SQL code!

You are very helpfull.
:D

Kristen
Test

22859 Posts

Posted - 2010-04-23 : 07:31:32

"I thought SQL Server used the same engine"

SQL Server is a far more sophisticated engine than Access. Access supports SQL syntax, but it isn't 100% compatible. There is an Upsize Tool to assist with migrating from Access to SQL Server - I don;t know if that would suggest changes to syntax for statements like this, but it might be worth a look.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-23 : 07:48:06

Sorry, so I have misinterpreted this: UPDATE A, B, C, D

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-23 : 08:35:08

quote:
Originally posted by webfred

Sorry, so I have misinterpreted this: UPDATE A, B, C, D

No, you're never too old to Yak'n'Roll if you're too young to die.

You are not the only one

. Same here, until i re-read the question

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources