Today, I was asked how complex update statements can be executed; so I built the following sample:
in line 1,2 I create a temporary table variable and add some values in the following lines. In line 10 it starts become interesting. Here we update the value b from table @t2 aliased as t2 and overwrite the values with the joined values of table @t1 aliased as t1. This syntax with froms and joins is much more common in select statements, but can be used in update statements as well.
The much worse solution is to create a cursor over @t1 and overwrite @t2 line by line. This solution is officially bad.
kr, Daniel
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | declare @t1 TABLE (a int, b int) declare @t2 TABLE (a int, b int) insert into @t1 values (1,2) insert into @t1 values (2,3) insert into @t2 values (1,200) insert into @t2 values (2,300) update t2 set t2.b = t1.b from @t1 as t1 inner join @t2 as t2 on t1.a = t2.a select * from @t1 select * from @t2 |
in line 1,2 I create a temporary table variable and add some values in the following lines. In line 10 it starts become interesting. Here we update the value b from table @t2 aliased as t2 and overwrite the values with the joined values of table @t1 aliased as t1. This syntax with froms and joins is much more common in select statements, but can be used in update statements as well.
The much worse solution is to create a cursor over @t1 and overwrite @t2 line by line. This solution is officially bad.
kr, Daniel
No comments:
Post a Comment