Sql-server – Merge Failed when update same rows

mergesql serversql-server-2012t-sql

The MERGE statement attempted to UPDATE or DELETE the same row more
than once. This happens when a target row matches more than one source
row. A MERGE statement cannot UPDATE/DELETE the same row of the target
table multiple times. Refine the ON clause to ensure a target row
matches at most one source row, or use the GROUP BY clause to group
the source rows.

I know this error can be fixed with update and not exists statements.

But i need this to be done with merge since merge will be faster than two statements.

Source:

+-----------+-----------+-------------------+---------+---------+----------+----------+
| Productid | Imagetype |     Imagename     | Website | Thumb90 | Thumb200 | Thumb500 |
+-----------+-----------+-------------------+---------+---------+----------+----------+
|   3144000 | small     | 3144000_small.jpg | Google  |       1 |        0 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       1 |        0 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       0 |        1 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       0 |        0 |        1 |
+-----------+-----------+-------------------+---------+---------+----------+----------+

Target:

+-----------+-----------+-------------------+---------+---------+----------+----------+
| Productid | Imagetype |     Imagename     | Website | Thumb90 | Thumb200 | Thumb500 |
+-----------+-----------+-------------------+---------+---------+----------+----------+
|   3144000 | small     | 3144000_small.jpg | Google  |       0 |        0 |        0 |
|   3144000 | medium    | 3144000_medium.jpg| Google  |       1 |        0 |        0 |
|   3144000 | large     | 3144000_large.jpg | Google  |       1 |        0 |        0 |
|   3144005 | small     | 3144005_small.jpg | Google  |       0 |        1 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       0 |        0 |        0 |
|   3144005 | large     | 3144005_large.jpg | Google  |       0 |        0 |        1 |
+-----------+-----------+-------------------+---------+---------+----------+----------+

Merge:

+-----------+-----------+-------------------+---------+---------+----------+----------+
| Productid | Imagetype |     Imagename     | Website | Thumb90 | Thumb200 | Thumb500 |
+-----------+-----------+-------------------+---------+---------+----------+----------+
|   3144000 | small     | 3144000_small.jpg | Google  |       1 |        0 |        0 |
|   3144000 | medium    | 3144000_medium.jpg| Google  |       1 |        0 |        0 |
|   3144000 | large     | 3144000_large.jpg | Google  |       1 |        0 |        0 |
|   3144005 | small     | 3144005_small.jpg | Google  |       0 |        1 |        0 |
|   3144005 | medium    | 3144005_medium.jpg| Google  |       1 |        1 |        1 |
|   3144005 | large     | 3144005_large.jpg | Google  |       0 |        0 |        1 |
+-----------+-----------+-------------------+---------+---------+----------+----------+

Query:

MERGE INTO [luannw2016].[ImageThumbnailTrack] AS TARGET
USING #ImageThumbnailTrack AS SOURCE ON TARGET.ProductId = SOURCE.ProductId
AND TARGET.ImageType = SOURCE.ImageType
WHEN MATCHED
THEN
    UPDATE
    SET ImageName  = SOURCE.ImageName,
        Website    = SOURCE.Website,
        Thumb90    = SOURCE.Thumb90,
        Thumb200   = SOURCE.Thumb200,
        Thumb500   = SOURCE.Thumb500
WHEN NOT MATCHED
THEN
    INSERT ([ProductId],[ImageType],ImageName,Website,Thumb90,Thumb200,Thumb500)
    VALUES ([ProductId],[ImageType],ImageName,Website,Thumb90,Thumb200,Thumb500);

Best Answer

It is no secret that I am not a fan of MERGE, for good reasons, and typically when I see customer code with MERGE, I re-write it using the "old" way of separate statements (and recommend others do the same, especially when they are not MERGE experts).

since merge will be faster than two statements.

NO IT WON'T. MERGE still runs the exact same semantics as separate statements would. There is no performance advantage at all. Please read up on it and be sure you understand how it works - if you are so confident MERGE is faster, then when you get your statement working, run some tests and try to prove it. I'm afraid you'll be disappointed with the results, but at least now your conclusion will be based on facts.

I think the error message is pretty clear; since ProductId,ImageType is not unique, you might need to add the ThumbXX columns to the comparison.

AND TARGET.Thumb90    = SOURCE.Thumb90
AND TARGET.Thumb200   = SOURCE.Thumb200
AND TARGET.Thumb500   = SOURCE.Thumb500

Also you should properly alias the VALUES() list:

WHEN NOT MATCHED
THEN
    INSERT ([ProductId],[ImageType],...)
    VALUES (SOURCE.[ProductId],SOURCE.[ImageType],...);

I would still write this as UPDATE ... WHERE EXISTS and then INSERT ... WHERE NOT EXISTS (and wrapped in an appropriate transaction of course, which your current MERGE statement needs but lacks).

Related Question