oracle sql: update if exists else insert

oracle, sql

Solution

MERGE doesn't need "multiple tables", but it does need a query as the source. Something like this should work:

MERGE INTO mytable d
USING (SELECT 1 id, 'x' name from dual) s
ON (d.id = s.id)
WHEN MATCHED THEN UPDATE SET d.name = s.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (s.id, s.name);

Alternatively you can do this in PL/SQL:

BEGIN
  INSERT INTO mytable (id, name) VALUES (1, 'x');
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UPDATE mytable
    SET    name = 'x'
    WHERE id = 1;
END;

Problem

Possible Duplicate: Oracle: how to UPSERT (update or insert into a table?) Hi, I have a table in which a record has to be modified if it already exists else a new record has to be inserted. Oracle sql doesnt accept `IF EXISTS`, otherwise I would have done an `if - update - else - insert` query. I've looked at `MERGE` but it only works for multiple tables. What do i do?

Original source

Related problems