SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 can i use join with Update ?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

allan8964
Posting Yak Master

196 Posts
Posted - 01/07/2013 :  13:21:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8515 Posts
Posted - 01/07/2013 :  13:36:31  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Edited by - webfred on 01/07/2013 13:55:12
Go to Top of Page

allan8964
Posting Yak Master

196 Posts
Posted - 01/07/2013 :  14:15:49  Show Profile  Reply with Quote
yes, tabel1 has tbl1Id and table2 has tbl1Id also to make them related. It's one to one relationship. thanks.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8515 Posts
Posted - 01/07/2013 :  14:35:40  Show Profile  Visit webfred's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000