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:

SQL: WITH clause (Common Table Expressions)

The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database. Not every database supports WITH clause. However, PostgreSQL dose support it. The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query. The name assigned to the sub-query is treated as though it is an inline view or table. The SQL WITH clause is basically a drop-in replacement to the normal sub-query.

WITH clause provides a way to write auxiliary statements for use in a larger query. These statements, which are often referred to as Common Table Expressions or CTEs, can be thought of as defining temporary tables that exist just for one query.

Syntax For The SQL WITH Clause

The following is the syntax of the SQL WITH clause when using a single sub-query alias.

WITH <alias_name> AS (sql_subquery_statement)SELECT column_list FROM <alias_name> [, tablename][WHERE <join_condition>]

When using multiple subquery aliases, the sysntax is as follows.

WITH <alias_name_A> AS (sql_subquery_statement),
<alias_name_B> AS(sql_subquery_statement_from_alias_name_A
or sql_subquery_statement )SELECT <column_list>FROM <alias_name_A>, <alias_name_B> [, tablename][WHERE <join_condition>]

The only advantage of using a CTE over a sub select is that you can actually name the sub query.

Following is an example.

WITH regional_sales AS (

      SELECT region, SUM(amount) AS total_sales
      FROM orders
      GROUP BY region), 
top_regions AS (
      SELECT region
      FROM regional_sales
      WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales))

SELECT region,
      product,
      SUM(quantity) AS product_units,
      SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

WITH Clause Is Most Useful For Recursion

When WITH clause is used to recursively perform a task, it can accomplish things not otherwise possible in standard SQL. In this case, a WITH query can refer to its own output. An example is this query to sum the integers from 1 through 100 shown below.

WITH  numbs(n) AS ( //recursively put all qualified n into numbs
      SELECT 1 from dual    //initialize the first value
      UNION ALL
      SELECT n+1 FROM numbs  //select from itself
      WHERE n < 100            //condition to stop
)
SELECT sum(n) FROM numbs;

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

                        

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 free here.

SQL: Keep finding the next missing value from a column of sequential data without inserting the previous/current missing value

You have a FRUIT table in your database. The data of the table look like below.

fruitKey      name                description
----------      -----------------  -------------------------------------
1                 Apple               Sells best in summer
5                 FruitA              Need to be frozen
6                 FruitB              Great
10               FruitC               Sweet
11               FruitD               For children
12               FruitF                Blue color
19               FruitG                Need to buy more

Now, you have more fruits coming in and you want to put them in your FRUIT table. You want the new records to fill in the fruitKey gaps instead of incrementing the max value of it. The following SQL will get the minimum missing fruitKey which is 2.

SELECT MIN(f1.fruitKey + 1) AS fruitKey
FROM FRUIT f1
Where NOT EXISTS (SELECT * FROM FRUIT f2
                                 WHERE f2.fruitKey = f1.fuitKey + 1;

However, you are required to summit all the insert statements at once for the system to process. The above SQL will not return the next missing fruitKey which is 3, instead, it will always return 2. The SQL below will return all the gaps in the fruitKey.

SELECT (f1.fruitKey + 1) AS fruitKey_low,
       (select min(f1.fruitKey) - 1 from FRUIT f3
            where f3.fruitKey > f1.fruitKey) AS fruitKey_high
FROM FRUIT f1
WHERE NOT EXISTS (SELECT * FROM FRUIT f2
                                 WHERE f2.fruitKey = f1.fuitKey + 1

The result looks like this.

fruitKey_low      fruitKey_hight
--------------       -----------------
2                       4
7                       9
13                     18

If you are creating the insert statements manually, you can pick the values from the above result as your fruitKeys. But most likely, you are going to write a program to create the insert statements and submit them to the database automatically. In this case, you need the SQL below to get the next missing fruitKey.

WITH G AS
(
SELECT (f1.fruitKey + 1) AS fruitKey_low,
       (select min(f3.fruitKey) - 1 from FRUIT f3
            where f3.fruitKey > f1.fruitKey) AS fruitKey_high
FROM FRUIT f1
WHERE NOT EXISTS (SELECT * FROM FRUIT f2
                                 WHERE f2.fruitKey = f1.fuitKey + 1
),
M AS
(
SELECT CASE
      WHEN (fruitKey_high - fruitKey_low >= 0) and (fruitKey_low > <current missing key>)                  THEN fruitKey_low
      WHEN (fruitKey_high - fruitKey_low > 0) and (fruitKey_low <= <current missing key>)
             and (fruitKey_high > <current missing key>)
      THEN <current missing key> + 1
      ELSE (select max(fruitKey)+1 from FRUIT)
      END AS fruitKey
FROM G
)
SELECT nvl (MIN(fruitKey), (select max(fruitKey)+1 from FRUIT)) AS fruitKey FROM M;

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

                        

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 free here.