CODEWITHSUNDEEP
sqlsql-server
Phaelax
Phaelax

Reputation: 2009

Merge table, track non-matches

I'm updating table A with table B (a temp table build from a csv file). B might contain records not found in A, if that's the case then I need to know. So either I need the record from B deleted after A is updated, or output B record when no match is found in A. I've never used MERGE before and just can't seem to get the syntax correct.

MERGE INTO table_A
USING table_B
ON table_A.id = table_B.id
WHEN MATCHED AND table_A.account = 2 THEN
    UPDATE SET
        tableA.col2 = tableB.col2,
        tableA.col3 = tableB.col3
    DELETE;


SELECT *
FROM table_B

Upvotes: 0

Views: 18

Answers (1)

Phaelax
Phaelax

Reputation: 2009

Not sure if this is the best approach, but I got the results I wanted.

CREATE TABLE tableC (  )


INSERT INTO tableC(col1, col2, col3)
SELECT col1, col2, col3
FROM (
    MERGE INTO table_A
    USING table_B
    ON table_A.id = table_B.id
    WHEN MATCHED AND table_A.account = 2 THEN
        UPDATE SET
            tableA.col2 = tableB.col2,
            tableA.col3 = tableB.col3
    OUTPUT tableC.*
) myoutput


SELECT *
FROM tableB
WHERE col1 NOT IN (
    SELECT col1
    FROM tableC
)

Upvotes: 1

Related Questions