Update table using conditions from multiple table

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

General SQL Server Forums

New to SQL Server Programming

Update table using conditions from multiple table

Author

Topic

kayal
Starting Member

2 Posts

Posted - 2010-01-11 : 12:26:03

Hello,

I am a beginner in SQL. I want to write a query for the below mentioned condition.

Three tables are there like tb01,tb02,tb03. I want to update tb01.col by copying the data from tb03.col. And to check the following conditions tb03.col2 = tb02.col2 and tb3.col3=tb01.col3.

I tried the below query but it throws some error..

update &db.tb01 A
set A.col = B.col
from &db.tb03 B, &db.tb02 C
where B.col2 = C.col2 and B.col3=A.col3

But in 3rd line (near from) it throws error.. I thought from is supported in sql2005 server. I am using DB2..

Can anyone help me on this issue..

Thanks on Advance,
Kayal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-11 : 12:28:51

[code]
update A
set A.col = B.col
from &db.tb01 A,&db.tb03 B, &db.tb02 C
where B.col2 = C.col2 and B.col3=A.col3
[/code]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 12:30:39

quote:
I thought from is supported in sql2005 server. I am using DB2..

What is this supposed to mean? Which RDBMS are you running the query agianst...
This is the syntax for SQL server..

update &db.tb01 A
set A.col = B.col
from 
&db.tb01 A inner join &db.tb03 B on B.col3=A.col3
inner join &db.tb02 C on B.col2 = C.col2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-11 : 12:33:02

quote:
Originally posted by vijayisonly

quote:
I thought from is supported in sql2005 server. I am using DB2..

What is this supposed to mean? Which RDBMS are you running the query agianst...
This is the syntax for SQL server..
update ~~&db.tb01~~ A
set A.col = B.col
from
&db.tb01 A inner join &db.tb03 B on B.col3=A.col3
inner join &db.tb02 C on B.col2 = C.col2

Nope you cant have both either use table name or alias alone with update

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-11 : 12:38:17

Oops..Sorry..missed that..

@kayal - Depending on the version of Db2..it may ..or may not be possible to do this kind of update.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-11 : 12:43:24

quote:
Originally posted by vijayisonly

Oops..Sorry..missed that..

@kayal - Depending on the version of Db2..it may ..or may not be possible to do this kind of update.

No problem

kayal
Starting Member

2 Posts

Posted - 2010-01-12 : 01:30:38

Thanks for your reply!!

I already tried with inner join. But DB2 throws error at "From"..
Could you please give me some other solution for the same.

Thanks on Advance,
Kayal

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-12 : 01:36:05

quote:
Originally posted by kayal

Thanks for your reply!!

I already tried with inner join. But DB2 throws error at "From"..
Could you please give me some other solution for the same.

Thanks on Advance,
Kayal

we repeat

Are you running this in sql server or db2?
if latter, please post this in db2 forums.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-12 : 09:15:04

quote:
Originally posted by kayal

Thanks for your reply!!

I already tried with inner join. But DB2 throws error at "From"..
Could you please give me some other solution for the same.

Thanks on Advance,
Kayal

If its DB2..what version of Db2 are you using?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources