OUTPUT clause with an update

Question

OUTPUT clause with an update

Carlo Romagnano

SSC-Insane

Points: 22600
More actions
June 20, 2017 at 11:12 pm

#337682

Comments posted to this topic are about the item OUTPUT clause with an update

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

tom.w.brannon Hall of Fame Points: 3956 More actions · Answer 1

I don't understand why deleted only shows the first row from each group. The join is to all rows in the group. I changed the values on description and it was the first row in each group returned. I would not want it to do the update to #b once for each instance of cod matching but it seems like all of #a should have been output. Can somebody help me understand?

George Vobr SSCrazy Eights Points: 9916 More actions · Answer 2

Thanks Carlo for such a interesting prepared question. I had a little help in an article on this topic recently mentioned in the Newsletter section.😉

Carlo Romagnano SSC-Insane Points: 22600 More actions · Answer 3

tom.w.brannon - Wednesday, June 21, 2017 5:45 AM
I don't understand why deleted only shows the first row from each group. The join is to all rows in the group. I changed the values on description and it was the first row in each group returned. I would not want it to do the update to #b once for each instance of cod matching but it seems like all of #a should have been output. Can somebody help me understand?

As from the explanation:

The OUTPUT clause returns in the INSERTED and DELETED tables only the rows touched. Any joined table (with 1-n) always returns one row and you can't predict which one.

sknox SSChampion Points: 12388 More actions · Answer 4

tom.w.brannon - Wednesday, June 21, 2017 5:45 AM
I don't understand why deleted only shows the first row from each group. The join is to all rows in the group. I changed the values on description and it was the first row in each group returned. I would not want it to do the update to #b once for each instance of cod matching but it seems like all of #a should have been output. Can somebody help me understand?

Logical order of operations:

1. The join happens, creating 14 rows
2. The where clause happens. In this case it doesn't filter anything, so we still have 14 rows.
3. The update happens. Here SQL Server will not actually change a row in table b more than once. The original row will be put in DELETED, and the final values for the row will be put in INSERTED. Table a is along for the ride here. Whatever row in table a happens to be joined to the row in table b which is selected to represent the delete or insert will show in the results.

The key is that table a is NOT joined in the OUTPUT clause, but before UPDATE filters its work list to one delete and insert per row. So that filter applies to the rows from table a as well. With no definition for the filter, it's arbitrary. If some operation caused a sort on the data before the update, you might not see what you consider the "first" row.

tom.w.brannon Hall of Fame Points: 3956 More actions · Answer 5

sknox - Wednesday, June 21, 2017 6:55 AM
tom.w.brannon - Wednesday, June 21, 2017 5:45 AM
I don't understand why deleted only shows the first row from each group. The join is to all rows in the group. I changed the values on description and it was the first row in each group returned. I would not want it to do the update to #b once for each instance of cod matching but it seems like all of #a should have been output. Can somebody help me understand?
Logical order of operations:
1. The join happens, creating 14 rows
2. The where clause happens. In this case it doesn't filter anything, so we still have 14 rows.
3. The update happens. Here SQL Server will not actually change a row in table b more than once. The original row will be put in DELETED, and the final values for the row will be put in INSERTED. Table a is along for the ride here. Whatever row in table a happens to be joined to the row in table b which is selected to represent the delete or insert will show in the results.
The key is that table a is NOT joined in the OUTPUT clause, but before UPDATE filters its work list to one delete and insert per row. So that filter applies to the rows from table a as well. With no definition for the filter, it's arbitrary. If some operation caused a sort on the data before the update, you might not see what you consider the "first" row.

Thank you! Now I understand.