How to do an update from a select with summarized registers in SQLServer

A very powerful way to do a table's update in a SQL Server database is to link it with another table with a join, and updating the fields of everyone of its registers using the field's values of the registers linked with the another one. This technique had been discussed in the forum post Update with join or update from select in SQL Server .

An example of Update with join:

Update table1
set table1field1 = t2.table2field1
from table1 t1, table2 t2
where t1.fieldX = t2.fieldX

If the field that is doing the join is key in the second table, there is no problem, the update will work perfectly. We are having a problem when it is not the case, and linking with the use of that field, to each register of the first table may correspond more than one of the latter. In that case, we can find unexpected results because the update will be done with the value of only one of the registers in the link with each register of the first table.

If the correspondence is from one to several registers and we want to save in every field of the first table an added value, such as the sum of all records that are mapped in the second table, we have to write a more complicated sentence.  

How to do an update with join from summarized registers

The we can think to solve it, is using directly the aggregation function in the SET, assignating the field value of the second table overwriting the preview from the first one. It would be something similar to the following: 

Update table1
set table1field1 = sum(t2.table2field1)
from table1 t1, table2 t2
where t1.fieldX = t2.fieldX

We have added the sum function in the assignment of the second field. It would be great if SQL Server would be capable of understand our intentions, but as a result of this, the software returns us the following message error:

Mess. 157, Level 15, Status 1, Line 2      
Aggregate may not appear in the set list of an UPDATE statement.

And then what do we do? A simple solution is to create a view or an alias that returns the result of the aggregation of the second table to the UPDATE, and make that the join of the update will be capable of find in this view a single register for each register in the table to update.

It's about creating a view or a table alias with the join of the two tables and the value added for each register in the source table where we want to do the update. 

The information we need are the key fields of each register of the table, and the calculated value for each of them.

This would be the SELECT with the identifiers and the added value for each record in the source table:

SELECT fieldX, SUM(t2.table2field1)
FROM table1 t1, table2 t2
WHERE t1.fieldX=t2.fieldX 
GROUP BY t1.fieldX

Then the Update statement using this select with an alias would be as follows:

UPDATE table1
SET table1field1 = table2summarized.field1summarized
FROM table1 t1, (SELECT fieldX, SUM(t2.table2field1) field1summarized
                          FROM table1 t1, table2 t2
                          WHERE t1.fieldX=t2.fieldX
                          GROUP BY t1.fieldX)  table2summarized
WHERE t1.fieldX = table2summarized.fieldX

And so for each record in the first table, to field1 will be assigned the sum of the values ​​from table2field1 that will be linked doing the join with the other table by fieldX.

It can be very useful when we want to update table fields with values ​​from other tables at different levels of aggregation. For example, the amount in one table with the invoice headers, calculated from the amounts of another table containing the lines or the detail of those bills.

Do you know or you think of a more efficient, smarter or simply different to get the same?

Do you find useful this SQL tip? Use the 'SHARE' button to share with your colleagues! 

By Carlos Fernández