Understanding SQL Merge statement?

Question

I have a source table that has data identical to my target table. When I try to run a merge statement, it fails with the error

merge can't update a target row multiple times.

So My Question is since they are identical why SQL did succeed but with 0 rows affected instead. Please help me understand this.

By the way, My syntax is correct because in my initial insert it succeeded, the problem is if re-run it again.

Thank you.

target table and the source table has the same data.

WHEN MATCHED AND ISNULL(T.VALUE,'') <> ISNULL(S.VALUE,'')

COL1          COL2    COL3   VALUE      DATE
1              A       TYPE    3       2019-01-02
2              B       KIND    4       2019-01-03
1              A       COLOR   0       2019-01-02
2              B       KIND    0       2019-01-03

MERGE TargetTable T
    USING
        (
            SELECT   COL1,
                     COL2,
                     COL3,
                     VALUE,
                     DATE    
            FROM SourceTable S
        ) s
        ON
        (                   
                    S.COL1  = T.COL1
                AND S.COL2  = T.COL2
                AND S.COL3  = T.COL3
                AND S.DATE  = T.DATE                                    
        )

    WHEN MATCHED AND
        (                   
            ISNULL(S.VALUE,'')  <> ISNULL(T.VALUE,'')
        )
    THEN UPDATE
        SET                             
            T.VALUE  = S.VALUE                          

    WHEN NOT MATCHED
    THEN INSERT VALUES
        (                   
             S.COL1 
            ,S.COL2 
            ,S.COL3
            ,S.VALUE    
            ,S.DATE
        );

Answer 1

I couldn't reproduce the error, but found something interesting

SQL DEMO

As you mention the first merge run perfect, but in my case the second merge says update 2 rows.

So I modify the 2nd merge to detect what rows were updated.

WHEN MATCHED AND
    (                   
        ISNULL(S.VALUE,'')  <> ISNULL(T.VALUE,'')
    )
THEN UPDATE
 SET T.VALUE  = S.VALUE + 10

OUTPUT

+------+------+-------+-------+---------------------+
| COL1 | COL2 | COL3  | VALUE |        DATE         |
+------+------+-------+-------+---------------------+
|    1 | A    | TYPE  |     3 | 02/01/2019 00:00:00 |
|    2 | B    | KIND  |    10 | 03/01/2019 00:00:00 |
|    1 | A    | COLOR |     0 | 02/01/2019 00:00:00 |
|    2 | B    | KIND  |    14 | 03/01/2019 00:00:00 |
+------+------+-------+-------+---------------------+

Because you have 2 rows with the exact match (COL1, COL2, COL3, DATE) the system is telling you don't know which one update with which row.

But that doesn't explain why on my demo work as expected.

So my suggestion is you have to add a PK to your table to make sure the merge happen on the right rows.

Answer 2

For better Unserstanding of Merge :

MERGE is a DML statement (data manipulation language). Also called UPSERT (Update-Insert). It tries to match source (table / view / query) to a target (table / updatable view) based on your defined conditions and then based on the matching results it insert/update/delete rows to/in/of the target table. MERGE (Transact-SQL)

create table src (i int, j int);
create table trg (i int, j int);

insert into src values (1,1),(2,2),(3,3);
insert into trg values (2,20),(3,30),(4,40);

merge into  trg
using       src
on          src.i = trg.i
when not matched by target then insert (i,j) values (src.i,src.j)
when not matched by source then update set trg.j = -1
when matched then update set trg.j = trg.j + src.j
;

select * from trg order by i

+---+----+
| i | j  |
+---+----+
| 1 | 1  |
+---+----+
| 2 | 22 |
+---+----+
| 3 | 33 |
+---+----+
| 4 | -1 |
+---+----+

Source : Stackoverflow SQL Merge