Merge with same target and source table

Question

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,

Should that first condition be WHEN MATCHED - when NOT MATCHED you won't have a row to update and will need to insert — JNK
– JNK, Commented Feb 7, 2012 at 15:08
please read question when nothing matches I want to insert and update — user570715
– user570715, Commented Feb 7, 2012 at 15:13
I did read it, and your proposed solution is nonsense. When there is not a match, you want to update what? There is no row to update. You want to update every other row in the table? — JNK
– JNK, Commented Feb 7, 2012 at 15:14
What is the "previous row"? Where does table alias b come from? you do not have a on condition, what do you want to use to match rows between source and target? — Mikael Eriksson
– Mikael Eriksson, Commented Feb 7, 2012 at 15:15

onedaywhen · Accepted Answer · 2012-02-07 15:39:52Z

4

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

answered Feb 7, 2012 at 15:39

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sebastian J. Over a year ago

That's IMHO the correct answer - at least it works for me for exact this question :)

Collectives™ on Stack Overflow

Merge with same target and source table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related