Search code examples
sqlsql-serversql-merge

Update with join throws "The MERGE statement attempted to UPDATE or DELETE the same row more than once."

I have an UPDATE query with a JOIN that's throwing the following error:

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.

Here's my update statement:

UPDATE
    Products
SET
    price = pu.ColumnValue
FROM
    Products p
    JOIN #priceupdates pu ON p.sku = pu.sku AND p.idManufacturer = pu.idManufacturer

Products joins on #priceupdates by SKU and idManufacturer.

There are no duplicates or NULLs in my #priceupdates table.

Edit:

To prove it, I ran the following 2 statements, and they both returned the same amount of records:

select sku, idmanufacturer from #priceupdates
select distinct sku, idmanufacturer from #priceupdates

End Edit

Depending on the data in #priceupdates it does or does not throw the error, but I'm having a hard time finding which data is the source of the problem.

So, besides for having duplicates, what other factors can cause this error?

Any help is appreciated.

Edit 2:

Here's test data. I can't give you a repro because as I said I can't find which data is giving me the problem.

CREATE TABLE #Products (idProduct INT, SKU VARCHAR(50), idManufacturer INT, Price FLOAT)
INSERT INTO #Products
SELECT '316992', '00015', '123', '0.52'
UNION SELECT '316993', '00037', '123', '0.52'
UNION SELECT '316994', '00039', '123', '0.52'
UNION SELECT '316995', '00115', '123', '0.52'
UNION SELECT '316996', '00137', '123', '0.52' 

CREATE TABLE #PriceUpdates (ProductUpdateID INT, SKU VARCHAR(50), idManufacturer INT, ColumnName VARCHAR(50), ColumnValue VARCHAR(50), idUser INT, BatchID INT)
INSERT INTO #PriceUpdates
SELECT '1837', '00015', '123', 'Price', '0.99', '13', '1929'
UNION SELECT '1838', '00037', '123', 'Price', '0.99', '13', '1929'
UNION SELECT '1839', '00039', '123', 'Price', '0.99', '13', '1929'
UNION SELECT '1840', '00115', '123', 'Price', '0.99', '13', '1929'
UNION SELECT '1841', '00137', '123', 'Price', '0.99', '13', '1929' 

UPDATE
    p
SET
    price = pu.ColumnValue 
FROM
    #Products p
    JOIN #priceupdates pu ON p.sku = pu.sku AND p.idManufacturer = pu.idManufacturer

 DROP TABLE #Products
 DROP TABLE #PriceUpdates

UPDATE!

I think I found the culprit. When I exclude a specific record from the priceupdates table, it works! But I still don't know what's wrong with that record. Where do I go from here? NOTE: when I only include that record, the update works as well.

Solution

  • My comment turned out to be an answer:

    I don't see a MERGE statement in your code. Does the error really call it MERGE when using an UPDATE? Hard to believe. Also, UPDATEs don't have that message at all. The message clearly refers to a MERGE and an ON clause.

    The UPDATE is not the problem. Look elsewhere.