need help understanding UPDATE...FROM...

Question

hello,

please help me to understand how the T-SQL's UPDATE FROM query works. the sample query below results in

value-inc, value-dec
value-inc, value-dec
value-inc, value-dec
value-inc, value-dec
value-inc, value-dec
value-inc, value-dec

i expected this:

null,        'value-dec'
null,        'value-dec'
null,        'value-inc'
null,        'value-inc'
null,        null
'value-inc', 'value-dec'

where am i wrong? thanks konstantin

declare @t table (s1 varchar(10), s2 varchar(10));

insert into @t
select null, null
union all
select null, null
union all
select null, null
union all
select null, null
union all
select null, null
union all
select null, null;

update @t
set s1 = x.s1, s2 = x.s2
from (select null as s1, 'value-dec' as s2
      union all
      select null as s1, 'value-dec' as s2
      union all
      select null as s1, 'value-inc' as s2
      union all
      select null as s1, 'value-inc' as s2
      union all
      select null as s1, null as s2
      union all
      select 'value-inc' as s1, 'value-dec' as s2) as x;

select * from @t;

Answer 1

Update from just allows you to build up a set of joins to define what you want to update, in the same way that you select from. e.g.

update t1
set t1.SomeField=t2.SomeDield
from Table1 t1
inner join table2 t2 on t1.id=t2.id

In your example it wouldn't really work because there is no way to join the subquery to the table variable.

Answer 2

As mentioned in Ben Robinson's answer, you need a common value between the two tables to perform the update in the way that you expect. I've updated your original example, adding an id column to your table. That id is then used as the join condition for the update as illustrated in the code below. Hopefully this example will make things clearer for you.

declare @t table (id int identity, s1 varchar(10), s2 varchar(10));

insert into @t
select null, null
union all
select null, null
union all
select null, null
union all
select null, null
union all
select null, null
union all
select null, null;

update t
set s1 = x.s1, s2 = x.s2
from @t as t
inner join (select 1 as id, null as s1, 'value-dec' as s2
            union all
            select 2 as id, null as s1, 'value-dec' as s2
            union all
            select 3 as id, null as s1, 'value-inc' as s2
            union all
            select 4 as id, null as s1, 'value-inc' as s2
            union all
            select 5 as id, null as s1, null as s2
            union all
            select 6 as id, 'value-inc' as s1, 'value-dec' as s2) as x
on t.id = x.id;

select * from @t;