7

I have this simple example I can't seems to get working : 

MERGE INTO mytable  mt
USING dual
ON (mt.id = 'AAA' )  
WHEN MATCHED THEN 
    UPDATE SET mt.name = 'updated'
WHEN NOT MATCHED THEN 
    INSERT (mt.id , mt.name )
    VALUES ('AAA', 'Gooood' );

If a 'AAA' record exists in the table, it is updated successfully.

But if does not exists, it is not inserted : 

Affected rows: 0
Time: 0.003ms

Any clue on what I am doing wrong ?

Jalil
  • 3,150
  • 3
  • 30
  • 39
  • What tool are you using that responds "Affected rows: 0 Time: 0.003ms"? – Tony Andrews Feb 25 '10 at 18:10
  • One wonders if the OP is merging without commit in one session and querying from another. Or whether the existing app is correctly interpreting output from the analog of SQL%ROWCOUNT. – Adam Musch Feb 25 '10 at 18:39
  • Is it possible your table `mytable` requires more than just `id` and `name` to do a valid insert? – Doug Porter Feb 25 '10 at 19:28
  • @Tony Andrews : Sorry for late answer, I had to do it another way ... The tool I used back then was Navicat for Oracle. – Jalil Mar 10 '10 at 16:18

1 Answers1

8

Works for me:

SQL> create table mytable (id varchar(3), name varchar(30));

Table created.

SQL> MERGE INTO mytable  mt
  2  USING dual
  3  ON (mt.id = 'AAA' )  
  4  WHEN MATCHED THEN 
  5      UPDATE SET mt.name = 'updated'
  6  WHEN NOT MATCHED THEN 
  7      INSERT (mt.id , mt.name )
  8      VALUES ('AAA', 'Gooood' );

1 row merged.

SQL> select * from mytable;

ID  NAME
--- ------------------------------
AAA Gooood
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259