CODEWITHSUNDEEP
sql-servermergemultiple-conditions
Raven Knit
Raven Knit

Reputation: 135

Merging with conditions (SQL Server)

I have a two tables, TABLE A and TABLE B with the same format. I'm running a merge statement using Table B as a source

TYPE    ITEM    QTY1    QTY2    QTY3
A       AAA     100     200     300
A       BBB     150     250     310
B       CCC     101     123     131
C       DDD     102     55      12

The thing is, I want to update specific columns only if the record already exists in the target table. Here's what I wanted to do, but I can't seem to get it to work. I just wanted to know, is this possible using the MERGE Statement?

MERGE TABLE A
    USING (SELECT * FROM TABLE B) B
    ON B.TYPE = A.TYPE AND B.ITEM = A.ITEM
    WHEN NOT MATCHED THEN
        INSERT (TYPE, ITEM, QTY1, QTY2, QTY3) VALUES (B.TYPE, B.ITEM, B.QTY1, B.QTY2, B.QTY3)
    WHEN MATCHED THEN
        CASE
            WHEN 'A' : UPDATE SET A.QTY1 = B.QTY1, A.QTY2 = B.QTY2, A.QTY3 = B.QTY3
            WHEN 'B' : UPDATE SET A.QTY2 = B.QTY2 , A.QTY3 = B.QTY3
            WHEN 'C' : UPDATE SET A.QTY3 = B.QTY3
        END;

Upvotes: 2

Views: 1635

Answers (1)

lc.
lc.

Reputation: 116458

You can add a condition to the WHEN MATCHED clause, and have multiple of these:

MERGE [TABLE A]
USING (SELECT * FROM [TABLE B]) B
ON B.TYPE = A.TYPE AND B.ITEM = A.ITEM
WHEN NOT MATCHED THEN
    INSERT (TYPE, ITEM, QTY1, QTY2, QTY3) VALUES (B.TYPE, B.ITEM, B.QTY1, B.QTY2, B.QTY3)
WHEN MATCHED AND A.TYPE = 'A' THEN
    UPDATE SET A.QTY1 = B.QTY1, A.QTY2 = B.QTY2, A.QTY3 = B.QTY3
WHEN MATCHED AND A.TYPE = 'B' THEN
    UPDATE SET A.QTY2 = B.QTY2 , A.QTY3 = B.QTY3
WHEN MATCHED AND A.TYPE = 'C' THEN
    UPDATE SET A.QTY3 = B.QTY3;

For more information, see the documentation.

Upvotes: 1

Related Questions