Merge Statement to updated duplicate rows also

Bineesh Viswanath

I have wrote a merge statement to update/Insert. its working well when i remove a column from the query.

MERGE TBLCONTRACTORS AS TARGET
USING [ips].dbo.TBLCONTRACTORS AS SOURCE
ON (TARGET.CONTRACTOR = SOURCE.CONTRACTOR)
WHEN MATCHED AND TARGET.PREFIX <> SOURCE.PREFIX
THEN UPDATE SET TARGET.PREFIX = SOURCE.PREFIX
WHEN NOT MATCHED BY TARGET
THEN INSERT (CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME,PREFIX) VALUES (SOURCE.CONTRACTOR,SOURCE.ABBREVIATION, (SELECT TOP 1 ID FROM Locations WHERE location=(select top 1 location from [ips].dbo.TBLLOCATIONS WHERE LOCATIONID=SOURCE.LOCATIONID)), SOURCE.VENDORNO, SOURCE.TRACKNAME,SOURCE.PREFIX);

Above code is working when i change value in source table. but below code gives 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.

TARGET.ABBREVIATION <> SOURCE.ABBREVIATION is the line where i getting error. This is i know, becuase of same value is on multiple rows for merging process. Please help me on this. How i can avoid this error by adding the line mentioned in red above.

MERGE TBLCONTRACTORS AS TARGET
USING [ips].dbo.TBLCONTRACTORS AS SOURCE
ON (TARGET.CONTRACTOR = SOURCE.CONTRACTOR)
WHEN MATCHED AND TARGET.PREFIX <> SOURCE.PREFIX OR TARGET.ABBREVIATION <> SOURCE.ABBREVIATION
THEN UPDATE SET TARGET.PREFIX = SOURCE.PREFIX , TARGET.ABBREVIATION= SOURCE.ABBREVIATION
WHEN NOT MATCHED BY TARGET
THEN INSERT (CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME,PREFIX) VALUES (SOURCE.CONTRACTOR,SOURCE.ABBREVIATION, (SELECT TOP 1 ID FROM Locations WHERE location=(select top 1 location from [ips].dbo.TBLLOCATIONS WHERE LOCATIONID=SOURCE.LOCATIONID)), SOURCE.VENDORNO, SOURCE.TRACKNAME,SOURCE.PREFIX);

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naimish Makwana3y ago

Accepted answer

Hello Beenish,

try below query.

WITH tempSource AS (
  SELECT CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME, PREFIX,
    ROW_NUMBER() OVER (PARTITION BY CONTRACTOR ORDER BY [DATE] DESC) AS rn
  FROM [ips].dbo.TBLCONTRACTORS
)
MERGE TBLCONTRACTORS AS TARGET
USING (
  SELECT CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME, PREFIX
  FROM tempSource
  WHERE rn = 1
) AS SOURCE
ON (TARGET.CONTRACTOR = SOURCE.CONTRACTOR)
AND (TARGET.ABBREVIATION <> SOURCE.ABBREVIATION OR TARGET.PREFIX <> SOURCE.PREFIX)
WHEN MATCHED THEN
  UPDATE SET
    TARGET.PREFIX = SOURCE.PREFIX,
    TARGET.ABBREVIATION = SOURCE.ABBREVIATION
WHEN NOT MATCHED BY TARGET THEN
  INSERT (CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME, PREFIX)
  VALUES (SOURCE.CONTRACTOR, SOURCE.ABBREVIATION, 
          (SELECT TOP 1 ID FROM Locations WHERE location=(select top 1 location from [ips].dbo.TBLLOCATIONS WHERE LOCATIONID=SOURCE.LOCATIONID)), 
          SOURCE.VENDORNO, SOURCE.TRACKNAME, SOURCE.PREFIX);

Thanks

Naimish Makwana

Bineesh Viswanath3y ago
Thanks a lot Naimish Makwana. I did few alteration and its working fine.

WITH tempSource AS (
SELECT CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME, PREFIX,
ROW_NUMBER() OVER (PARTITION BY CONTRACTOR ORDER BY [LOCATIONID] DESC) AS rn
FROM [ips].dbo.TBLCONTRACTORS
)
MERGE TBLCONTRACTORS AS TARGET
USING (
SELECT CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME, PREFIX
FROM tempSource
WHERE rn = 1
) AS SOURCE
ON (TARGET.VENDORNO = SOURCE.VENDORNO AND TARGET.LOCATIONID=(SELECT TOP 1 ID FROM Locations WHERE location=(select top 1 location from [ips].dbo.TBLLOCATIONS WHERE LOCATIONID=SOURCE.LOCATIONID)))

WHEN MATCHED AND (TARGET.CONTRACTOR <> SOURCE.CONTRACTOR OR TARGET.ABBREVIATION <> SOURCE.ABBREVIATION OR TARGET.PREFIX <> SOURCE.PREFIX) THEN
UPDATE SET
TARGET.PREFIX = SOURCE.PREFIX,
TARGET.ABBREVIATION = SOURCE.ABBREVIATION,
TARGET.CONTRACTOR=SOURCE.CONTRACTOR
WHEN NOT MATCHED BY TARGET AND SOURCE.VENDORNO NOT IN(SELECT ISNULL(VENDORNO,0) FROM TBLCONTRACTORS) THEN
INSERT (CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME, PREFIX)
VALUES (SOURCE.CONTRACTOR, SOURCE.ABBREVIATION,
(SELECT TOP 1 ID FROM Locations WHERE location=(select top 1 location from [ips].dbo.TBLLOCATIONS WHERE LOCATIONID=SOURCE.LOCATIONID)),
SOURCE.VENDORNO, SOURCE.TRACKNAME, SOURCE.PREFIX);

Tuhin Paul3y ago

you can try adding another condition to the ON clause that ensures that the rows are matched uniquely:

MERGE TBLCONTRACTORS AS TARGET
USING [ips].dbo.TBLCONTRACTORS AS SOURCE
ON (TARGET.CONTRACTOR = SOURCE.CONTRACTOR) 
    AND (TARGET.ABBREVIATION = SOURCE.ABBREVIATION OR (TARGET.ABBREVIATION IS NULL AND SOURCE.ABBREVIATION IS NULL))
    AND (TARGET.PREFIX = SOURCE.PREFIX OR (TARGET.PREFIX IS NULL AND SOURCE.PREFIX IS NULL))
WHEN MATCHED THEN 
    UPDATE SET 
        TARGET.PREFIX = SOURCE.PREFIX,
        TARGET.ABBREVIATION = SOURCE.ABBREVIATION
WHEN NOT MATCHED BY TARGET THEN 
    INSERT (
        CONTRACTOR,
        ABBREVIATION,
        LOCATIONID,
        VENDORNO,
        TRACKNAME,
        PREFIX
    ) VALUES (
        SOURCE.CONTRACTOR,
        SOURCE.ABBREVIATION,
        (
            SELECT TOP 1 ID 
            FROM Locations 
            WHERE location = (
                SELECT TOP 1 location 
                FROM [ips].dbo.TBLLOCATIONS 
                WHERE LOCATIONID=SOURCE.LOCATIONID
            )
        ),
        SOURCE.VENDORNO,
        SOURCE.TRACKNAME,
        SOURCE.PREFIX
    );

The ON clause checks for a unique match by comparing the ABBREVIATION and PREFIX columns in addition to the CONTRACTOR column. It also checks for cases where both the ABBREVIATION and PREFIX columns are null in both the source and target tables. This should ensure that each target row matches at most one source row, and the error message should no longer occur.

Sachin Singh3y ago

Error is because of the OR statement, it will always return more than one row , if you can use AND then the error may resolve.

MERGE TBLCONTRACTORS AS TARGET
USING [ips].dbo.TBLCONTRACTORS AS SOURCE
ON (TARGET.CONTRACTOR = SOURCE.CONTRACTOR) AND (TARGET.PREFIX <> SOURCE.PREFIX) AND(TARGET.ABBREVIATION <> SOURCE.ABBREVIATION)
WHEN MATCHED
THEN UPDATE SET TARGET.PREFIX = SOURCE.PREFIX , TARGET.ABBREVIATION= SOURCE.ABBREVIATION
WHEN NOT MATCHED BY TARGET
THEN INSERT (CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME,PREFIX) VALUES (SOURCE.CONTRACTOR,SOURCE.ABBREVIATION, (SELECT TOP 1 ID FROM Locations WHERE location=(select top 1 location from [ips].dbo.TBLLOCATIONS WHERE LOCATIONID=SOURCE.LOCATIONID)), SOURCE.VENDORNO, SOURCE.TRACKNAME,SOURCE.PREFIX);

Bineesh Viswanath3y ago
Saching Singh,

Its getting the same error

Sachin Singh3y ago

test this

MERGE TBLCONTRACTORS AS TARGET
USING [ips].dbo.TBLCONTRACTORS AS SOURCE
ON (TARGET.CONTRACTOR = SOURCE.CONTRACTOR) AND (TARGET.PREFIX <> SOURCE.PREFIX OR TARGET.ABBREVIATION <> SOURCE.ABBREVIATION)
WHEN MATCHED 
THEN UPDATE SET TARGET.PREFIX = SOURCE.PREFIX , TARGET.ABBREVIATION= SOURCE.ABBREVIATION
WHEN NOT MATCHED BY TARGET
THEN INSERT (CONTRACTOR, ABBREVIATION, LOCATIONID, VENDORNO, TRACKNAME,PREFIX) VALUES (SOURCE.CONTRACTOR,SOURCE.ABBREVIATION, (SELECT TOP 1 ID FROM Locations WHERE location=(select top 1 location from [ips].dbo.TBLLOCATIONS WHERE LOCATIONID=SOURCE.LOCATIONID)), SOURCE.VENDORNO, SOURCE.TRACKNAME,SOURCE.PREFIX);