can i use join with Update ? - SQL Server Forums

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

can i use join with Update ?

Author

Topic

allan8964
Posting Yak Master

249 Posts

Posted - 2013-01-07 : 13:21:56

Hi there,

I have a table1 which has cols as

SaleDate, Qty, Model

and table2 which has cols as

Total ...

in db. Now I need update table2 with some data in table1 like this:

update table2
set total = Qty * 0.13
where SaleDate > '2012-10-12' and Model = 'AAA'

can I use join here? or How can I make this happen?
Thanks in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-01-07 : 13:36:31

join on which column? Is there an ID?

We also need to know if there is a one to many relationship ...

Best would be to give us table structure, example data and wanted result...

Too old to Rock'n'Roll too young to die.

allan8964
Posting Yak Master

249 Posts

Posted - 2013-01-07 : 14:15:49

yes, tabel1 has tbl1Id and table2 has tbl1Id also to make them related. It's one to one relationship. thanks.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-01-07 : 14:35:40

normally I script it like this:
update t2
set total = t1.qty * 0.13
from table2 as t2
join table1 as t1 on t1.tbl1Id = t2.tbl1Id
where t1.SaleDate > '20121012' and t1.Model = 'AAA'

But in this case you can also take the other conditions into to the ON clause:
update t2
set total = t1.qty * 0.13
from table2 as t2
join table1 as t1 on t1.tbl1Id = t2.tbl1Id and t1.SaleDate > '20121012' and t1.Model = 'AAA'

Too old to Rock'n'Roll too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources