userimage

Oracle: how to UPSERT (update or insert into a table?)

The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data:

if table t has a row exists that has key X:

    update t set mystuff... where mykey=X

else

    insert into t mystuff...

Since Oracle doesn"t have a specific UPSERT statement, what"s the best way to do this?

 

userimage

MERGE INTO employees e

    USING hr_records h

    ON (e.id = h.emp_id)

  WHEN MATCHED THEN

    UPDATE SET e.address = h.address

  WHEN NOT MATCHED THEN

    INSERT (id, address)

    VALUES (h.emp_id, h.address);    

 

Answer is