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
Mess. 157, Level 15, Status 1, Line 2
Aggregate may not appear in the set list of an UPDATE statement.
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.
Do you find useful this SQL tip? Use the 'SHARE' button to share with your colleagues!
By Carlos Fernández