SQL: UPSERT syntax and examples

UPSERT is part of the data manipulation language (DML) that allows one to use a single statement to atomically either INSERT a row, or on the basis of the row already existing, UPDATE that row instead, while safely giving little to no further thought to concurrency. The "essential property of UPSERT" is that one of those two outcomes must be guaranteed, regardless of concurrent activity.

PostgreSQL:

Starting PostgreSQL 9.5, UPSERT becomes part of its DML.

Use the ON CONFLICT clause:

For example, you have a store table and you want to set the store name to 'Unforgettable Fruits' where the store id is 559. You can use the following SQL to guarantee the result regardless if store 559 already exists. 

INSERT INTO store (id, name) values (559, 'Unforgettable Fruits')
ON CONFLICT (id)
DO UPDATE SET name = 'Unforgettable Fruits';

Use the WITH clause:

Syntax:

WITH upsert AS
      (UPDATE <table name>
            SET <column name> = <value>
            WHERE <condition> RETURNING *)
      INSERT into <table name> (<columns separated by comma>)
            SELECT <values separated by comma>
            WHERE not exists (SELECT * from upsert);

 With the above store example:

WITH upsert AS
      (update store set name = 'Unforgettable Fruits' where id=559 returning *)                        insert into store (id, name) select 559, 'Unforgettable Fruits' where not exists (select * from upsert);

Oracle:

The above SQL starting with 'WITH upsert AS' does not work in Oracle. However Oracle can use the MERGE clause to do the same job. MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard.

Syntax:

MERGE into <table name>
USING
      <source table/view/result of sub-query>
ON
       <match condition>
WHEN MATCHED THEN
       <update clause>
       <delete clause>
WHEN NOT MATCHED THEN
      <insert clause>

Using the above store example.

merge into store using                                                                                                       (select id, name from store_temp b) on (a.id = b.id)                                               when matched then                                                                                                             update set a.name = b.name                                                                               when not matched then                                                                                                       insert (a.id, a.name) values (b.id, b.name); 

The MERGE clause is also supported in DB2 and MS SQL.

MySQL:

The ON DUPLICATE KEY UPDATE clause: Checks if a primary key or unique index is violated by an insert, it will then do an update.

Syntax:

INSERT into <table name> (<columns separated by comma>) values (<values separated by comma>)
ON DUPLICATE KEY <update clause>

Using the above store example, which has the id as the primary key:

INSERT into store (id, name) values (559, 'Unforgettable Fruits')
ON DUPLICATE KEY UPDATE name='Unforgettable Fruits';

Of course, you can always do a select first to check if the record exists, then decide if you need to update or insert. You can also do an update first, if it returns zero, then insert.

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.

Syntax: