oracle sql: update if exists else insert [duplicate]

Question

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?

Answer 1

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;