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
        );

Where is the merge statement? – Juan Carlos Oropeza Aug 07 '19 at 14:07 — Juan Carlos Oropeza, Aug 07 '19 at 14:07
you should tag the target DB engine. Is it SQl Server ? – iDevlop Aug 07 '19 at 14:19 — iDevlop, Aug 07 '19 at 14:19
sorry for not being aware of that, yes it SQL 2017 – sesy Aug 07 '19 at 14:22 — sesy, Aug 07 '19 at 14:22

score 0 · Answer 1 · answered Aug 07 '19 at 14:29

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

I think OP understand the basic of merge. But this doesn't help to explain the error. — Juan Carlos Oropeza, Aug 07 '19 at 14:38

score 0 · Answer 2 · answered Aug 07 '19 at 14:48

0

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.

answered Aug 07 '19 at 14:48

Juan Carlos Oropeza

47,252
12
78
118

Thank you, but what does +10 do to the value and could be any other number if i want?. I can't add PK that's not possible in this case. – sesy Aug 07 '19 at 14:53
As I explain I use +10 to detect which rows are being update even when they are the same value. You have 2 rows with same combination `(COL1, COL2, COL3, DATE)` lets call those `A1` with value `0` and `A2` with value `4` So when Target `A1` is match with Source `A1` nothing change because the value are the same. But when Target `A1` is matched with Source `A2` the value change. The same happen with Target `A2` – Juan Carlos Oropeza Aug 07 '19 at 15:07
Ok, So you use +10 just to detect the update fields. i could have used +4 and it should work fine right? – sesy Aug 07 '19 at 15:10
Juan, I have tried, your Demo, you are changing the value there by adding 10. if not you had changed the value by adding +10, this wouldn't have worked then? that was my question why? – sesy Aug 07 '19 at 15:17
Yes. Was just for debug propose so can see what was happening. If you remove the +10 on the **sql debug** you can see there were 2 rows affected. You will get the expected output. But in this case the 0 become 4 and the 4 become 0. That I consider is a weird behavior but isn't the error you reported. But I can see how they are related. Maybe is a setting on my platform vs yours and don't detect it as an error. – Juan Carlos Oropeza Aug 07 '19 at 15:20
Let's try it this [**WAY**](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f2661468a8defe25aa71aaf036c2d498) I add a column ID. Here you can see on first Merge (the insert) the ID 20 has value 4, but after second merge that ID has value 0 – Juan Carlos Oropeza Aug 07 '19 at 15:25
Thank you, Juan, I have voted your comment because it explains a different angle. – sesy Aug 07 '19 at 15:36
well, when I have added 4888 to ID 20 it updated ID 40. why not ID 20? – sesy Aug 07 '19 at 15:45
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=7637cf351b70d681a581f566b16a70eb – sesy Aug 07 '19 at 18:03
That is what I try to explain already and same happen in my sample. Read again the comment above when I talk about A1 and A2. You ahve this match condition `WHEN MATCHED AND ( ISNULL(S.VALUE,'') <> ISNULL(T.VALUE,'') )` A1 match A1 but are the same so doesn't update. But when A1 match with A2 update. Same with A2 match with A1. So values are swap – Juan Carlos Oropeza Aug 07 '19 at 18:15

Understanding SQL Merge statement?

2 Answers2