CODEWITHSUNDEEP
sqlsql-server-2008
user570715
user570715

Reputation: 639

Merge with same target and source table

I have one table where I want to check if record exists leave it alone, if not insert new row and update previous row. I am wondering if I can use merge here like below ?

  CREATE TABLE a
  (keycol INT PRIMARY KEY,
  col1 INT NOT NULL,
  col2 INT NOT NULL,
  col3 INT NOT NULL);

INSERT INTO a VALUES (1,0,0,0),(2,0,0,0);

MERGE INTO a
USING select 1 from a where col1 = 3
WHEN NOT MATCHED THEN
UPDATE SET
col2 = 2,
col2 = 2,
col3 = 2
where col1 = 3
WHEN NOT MATCHED THEN   
INSERT (keycol, col1, col2, col3)
VALUES (4, 0, 0, 0)

Thanks,

Upvotes: 1

Views: 3651

Answers (1)

onedaywhen
onedaywhen

Reputation: 57023

MERGE INTO a
   USING (
          VALUES (3,3,2,2),
                 (4,0,0,0)
         ) AS source (keycol, col1, col2, col3)
   ON a.keycol = source.keycol
      AND a.col1 = source.col1
WHEN MATCHED THEN
   UPDATE 
      SET col2 = source.col2,
          col3 = source.col3
WHEN NOT MATCHED THEN   
   INSERT (keycol, col1, col2, col3)
      VALUES (keycol, col1, col2, col3);

Upvotes: 4

Related Questions