0

I would like to know whether merge:

  1. Evaluates each row in the source table separately (i.e. performs the INSERT, UPDATE etc. for that row before moving onto the next row in the source table), or
  2. Evaluates all rows in the source table are at the 'same time'.

Example: in my source table, there may be two very similar records in the source table. If this is the case, I want the 'second' such record to result in an update to the row in the target table created using the 'first' record in the source table.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
JRyan
  • 121
  • 6
  • 2, and what is criteria for similarity? In software world, similarity concept as such doesn't exist. – Arvo Sep 05 '17 at 10:18
  • If two different rows match the same target row in merge, you get an error. You need to compose your source query correctly to yield the correct unique row – Nick.Mc Sep 05 '17 at 10:19
  • You need to clean up your source beforehand. This would be the cleaniest way to go for it. Have you tried to run a merge for your scenario? If there are duplicates on the matching column you should get an error anyway. – Rigerta Sep 05 '17 at 10:19
  • Your scenario can never work (at least not reliably). Obviously `MERGE` is implemented as 1 because it's not magic, but because it's a logical operation, the order in which rows are processed is unspecified, so you *may as well* act as if it were 2. Notably, it is an error for a `MERGE` to affect an existing row more than once (I'm not sure it performs the same detection for updating a new row). – Jeroen Mostert Sep 05 '17 at 10:20
  • 1
    SQL always tries to act *as if* all updates are applied in parallel - all columns in a row are updated at the same time, all rows are updated at the same time. Otherwise, you could encounter the [Halloween Problem](https://en.wikipedia.org/wiki/Halloween_Problem) – Damien_The_Unbeliever Sep 05 '17 at 10:22

0 Answers0