OUTPUT clause with an update

  • Carlo Romagnano

    SSC-Insane

    Points: 21873

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

  • tom.w.brannon

    Hall of Fame

    Points: 3956

    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: 9082

    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: 21873

    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: 12284

    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

    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.

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

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