Merge Error

Question

Dear all,

I'm trying to either insert or update data with the following conditions: only update the data if they exist with the ids which are equal, but only if they have data, and I'm getting the following error message:

[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]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.

Is there anything I'm missing?

USE [DATABASE]
GO

ALTER PROCEDURE [sp_name] AS
MERGE table1 f
USING table2 u
ON f.id = u.id
AND
f.[column1] IS NOT NULL
OR
f.[column2] IS NOT NULL

WHEN MATCHED THEN
UPDATE SET
f.[column1] = u.[column1],
t.[column2] = u.[column2],

WHEN NOT MATCHED THEN
INSERT (
[column1],
[column2]
)
VALUES (
[column1],
[column2]
);

Answer 1

You should not add business logic to your join condition.

I suspect you actually want this instead:

MERGE table1 f
USING table2 u
ON f.id = u.id

WHEN MATCHED AND
(f.[column1] IS NOT NULL
OR
f.[column2] IS NOT NULL) THEN
UPDATE SET
f.[column1] = u.[column1],
t.[column2] = u.[column2],

WHEN NOT MATCHED THEN
INSERT (
[column1],
[column2]
)
VALUES (
[column1],
[column2]
);

Answer 2

Is there anything I'm missing?

Yes, reading the error message:

The MERGE statement attempted to UPDATE or DELETE the same row more than once.

Seems your JOIN condition is't right.

Answer 3

Hi，@sql dev

Welcome to Microsoft T-SQL Q&A Forum!

Step1:I guess you only match one in the on condition. The two tables f and u must have fields that can be matched. You can observe the table when f.id = u.id, there must be many rows that match, so Just got this error.

Step2: More conditions should be inserted to perform one-to-one row matching between tables.

Step3: Modify the code

ALTER PROCEDURE [sp_name] AS  
    Merge into table1 f  
    Using table2 u  
    ON f.id = u.id  and f.column=u.column---The matching field is not unique, add a matching field  
    AND f.[column1] IS NOT NULL OR f.[column2] IS NOT NULL  
      
    WHEN MATCHED   
    THEN  
    UPDATE set f.[column1] = u.[column1],  
        f.[column2] = u.[column2],  
      
    WHEN NOT MATCHED   
    THEN  
    INSERT (column1,column2)VALUES ([column1],[column2])  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @sql dev

The MERGE statement ON clause is very questionable:

 ON f.id = u.id  
 AND f.[column1] IS NOT NULL  
 OR f.[column2] IS NOT NULL  

You need to keep just primary keys in the ON clause, and move any addition conditions to the WHEN MATCHED clause. Along the following:

WHEN MATCHED AND <additional conditions> THEN