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

Shambhu Rai 1,411 Reputation points
2022-11-17T05:34:23.707+00:00

ssis package error when loading last 10 years data

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.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,950 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,589 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 37,376 Reputation points
    2022-11-17T06:00:05.083+00:00

    Hi @Shambhu Rai

    What component are you using in your package? Are you using the T-sql code in the package to merge the data?

    Could you please share us your table structure (CREATE TABLE …) and some sample data (INSERT INTO …) along with the code you are using? So that we’ll get a right direction and make some test.

    The error message is clearly that you need to 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.

    Regards,

    Zoe Hui


    If the answer is helpful, please click "Accept Answer" and upvote it.

    0 comments No comments

  2. Jingyang Li 5,891 Reputation points
    2022-11-17T16:56:42.01+00:00

    When you run into this error, it is good to find your data issue with the MERGE statement.
    If you would use UPDATE... FROM... JOIN... syntax, you will not find this issue at all.
    Check your source data and investigate your business requirement and modify your source to get rid of this case with different options.

    This is a good case to promote to developers to use MERGE.
    It will take some efforts to learn this.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.