Search This Blog & Web

Thursday, December 3, 2009

Update Statement Using Join in from clause

Some time we need to update our table from another table join that cannot possible with simple select table. In sql server 2005 we can do this using update using join as I show this in my example.
Declare @vA table (id int,val varchar(50))
Declare @vb table (id int,val varchar(50))

Insert into @vA(id,val) values (1,'1111')
Insert into @vA(id,val) values(2,'1111')
Insert into @vA(id,val) values(3,'1111')
Insert into @vA(id,val) values(4,'1111')
Insert into @vA(id,val) values(5,'1111')
Insert into @vA(id,val) values(6,'1111')
Insert into @vB(id,val) values(1,'2222')
Insert into @vB(id,val) values(2,'4444')
Insert into @vB(id,val) values(3,'5555')
Insert into @vB(id,val) values(4,'3333')
Insert into @vB(id,val)values (5,'6666')
Insert into @vB(id,val) values(6,'7777')

Update @vA set val=d.val
from @vA a inner join @vB d on a.id = d.id

Select * from @vA


enjoy the output.

No comments: