Replace (Update/Insert) a row into Oracle table – Merge into

Shiji Pan

11 years ago

In Mysql, if you want to either updates or inserts a row in a table, depending if the table already has a row that matches the data, you can use “ON DUPLICATE KEY UPDATE”.

How to do it in Oracle?

Use “merge into” .

MERGE INTO employees USING dual ON ( "id"=123456 )
WHEN MATCHED THEN UPDATE SET "last_name"="smith" , "first_name"="bob"
WHEN NOT MATCHED THEN INSERT ("id","last_name","first_name")
    VALUES ( 123456,"smith", "bob" )

Replace (Update/Insert) a row into other DB:

In SQL Server, http://www.withdata.com/ad/sql-server/replace-update-or-insert-a-row-into-sql-server-table.html .

In DB2, http://www.withdata.com/ad/db2/replace-update-or-insert-a-row-into-db2-table-merge-into.html .

In PostgreSQL, http://www.withdata.com/ad/postgresql/replace-update-or-insert-a-row-into-postgresql-table.html

In Sqlite, http://www.withdata.com/ad/sqlite/replace-update-or-insert-a-row-into-sqlite-table.html