Transact sql, building a two-table update request

When updating the table with the update ... from ... construct, do not forget about such a factor as the number of rows in the source that correspond to one row in the target table. The options are "one to one" and "several to one." Consider an example.

Let there be tables

 create table #A ([match] int, [column] int); create table #B ([match] int, [column] int); 

We will update #A data from #B .

Option "One to One"

Add data to the tables:

 insert into #A values (1, 10); insert into #B values (1, 100); 

In this case, as the participant gofr1 has already indicated in his answer, we can use the query to update

 update A set A.[column] = B.[column] from #AA join #BB on B.[match] = A.[match]; 

Option "Several to one"

Add more data to the tables — such that one row in the target table matches several rows in the source:

 insert into #A values (2, 20); insert into #B values (2, 200); insert into #B values (2, 2000); 

Now, if we use the same query for the update as in the previous case, the update will, however, be no guarantee as to which of the values ​​(200 or 2000) will #A [column] in #A with [match] = 2 exists.

The design of the merge in this regard is more prudent. If we try using merge to do the same:

 merge into #AA using (select * from #B) B on A.[match] = B.[match] when matched then update set A.[column] = B.[column]; 

then we get the error Msg 8672:

The MERGE statement attempted to UPDATE or DELETE the same row more than once ...

Those. in the case of "several to one" before updating, it is necessary to bring the data to a "one to one" form in any suitable case For example, using aggregation

 ;with B ([match], [column]) as ( select [match], max([column]) from #B group by [match] ) update A set A.[column] = B.[column] from #AA join B on B.[match] = A.[match]; 

or additional filtering

 ;with B ([match], [column], rowNum) as ( select [match], [column], row_number() over (partition by [match] order by [column]) from #B ) update A set A.[column] = B.[column] from #AA join B on B.[match] = A.[match] and B.rowNum = 1;