MSSQL UPDATE

Here is how to do an update from in SQL Server. I am posting this because I always forget the syntax and I put the from after the update and before the set when really it should be the other way around.

Anyway this is useful for moving values from one table to another. I used to write cursors and things like that to accomplish the same thing when I didn't know about update from. This is much easier than writing a loop for simple updates.

 

Anyway this is useful for moving values from one table to another. I used to write cursors and things like that to accomplish the same thing when I didn't know about update from. This is much easier than writing a loop for simple updates.

declare @table1  table(ID int, Value varchar(25))

declare @table2  table(ID int, Value varchar(25))

 

insert into @table1 values(1, null)

insert into @table1 values(2, null)

insert into @table1 values(3, null)

 

insert into @table2 values(1, 'test1')

insert into @table2 values(2, 'test2')

insert into @table2 values(3, 'test3')

 

select * from @table1

select * from @table2

 

update t1

set t1.Value = t2.value

from @table1 as t1 inner join @table2 as t2 on t1.ID = t2.ID

 

 

select * from @table1

 

 

Here are the results of the above script:

--Table 1 Before

ID          Value

----------- -------------------------

1           NULL

2           NULL

3           NULL

 

--Table 2 Before

ID          Value

----------- -------------------------

1           test1

2           test2

3           test3

 

 

--Table 1 After

ID          Value

----------- -------------------------

1           test1

2           test2

3           test3