Using Subqueries in INSERT, UPDATE, DELETE Statements

Did you know that subqueries can also be used in UPDATE, INSERT, and DELETE statements? Subqueries in SQL are like building blocks that can be used in a variety of statements, not just SELECT. If you find subqueries useful in SELECT, read this article to find out how useful they can be in other instructions. You will be able to create much more complex and powerful SQL queries in no time!

If you want to refresh your knowledge of subqueries, I recommend the interactive SQL Practice Set course. It contains over 80 exercises on subqueries and other challenging SELECT constructions.

Starting Point: A Quick Review of Subqueries

Let’s start with a quick reminder of what a subquery is. Since subqueries are used most often in SELECT statements, we will review an example of a simple subquery in a SELECT statement.

We can define a subquery as a query within another query. While subqueries are used most often in the WHERE clause of SELECT statements, they can be used in several other clauses including WHERE, FROM, and HAVING, among others.

Let’s look at the database we will use as an example. Imagine that you are the owner of a wine shop, and you have a simple database with 3 tables to manage the shop operation.

The first table is wine, which stores the products you sell, with the name, the price, the number of bottles in stock, etc. for each wine. The second is order, which stores the orders we receive from our customers, including the name of the wine ordered and the quantity ordered, among other information.

wine

order

Suppose we want to obtain a list of wines for which we have never received an order. The query will look like this:

SELECT  name,
FROM    wine
WHERE   name NOT IN ( SELECT wine_name FROM order )

The subquery returns the names of all the wines for which we have received orders. Then the outer query, using the NOT IN operator, obtains the names of the wines never included in any order. SQL is as simple as it is powerful! If you want to brush up on some subquery concepts, I suggest the course SQL Basics where you can find a complete section on subqueries.

First Stop: Using Subqueries in INSERT Statements

Let’s now use a subquery in an INSERT statement. This is very common; the idea is to insert the complete result set from a subquery or a SELECT statement into a table. As an example, imagine we want to create invoices for all wine orders we received during the day. Below is a partial view of our invoice table:

Pretend that today’s date is June 28, 2020, and we want to insert the records of the invoices associated with today’s orders. We can use the following SELECT to generate the data for the invoices:

SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

The result of this query is below:

This is exactly what we want to insert into the table invoice.

If we simply add an INSERT clause before the query, we can insert the result of the query into the table wine, as we can see in the following example:

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

Note the following:

• We need to explicitly name the columns of the invoice table into which we are inserting.
• The columns in the SELECT list must be in the same order as the columns in the table.
• We omitted the column invoice_id to allow the database to choose the next value using a sequence generator by default.

After executing the INSERT, the table invoice will have the new invoice records for today’s orders. We can see this below, with the new records in red:

Suppose that on a particular day, we accidentally ran the INSERT twice, and our customers received two invoices for each order as a result. We don’t want to repeat the same error in the future! To prevent this, we add a subquery to the INSERT statement to see if an invoice with the same order_id already exists. The following is the new version of the INSERT query. The subquery added at the end identifies the invoices that already exist, and the outer query discards them by using the NOT IN operator.

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT order.date, 
       order.client_id, 
       o.quantity * w.price as amount, 
       o.quantity || ’ bottles of ‘ || o.wine_name,
       o.order_id
FROM   order o 
JOIN   wine ON wine.name = order.wine_name
WHERE  order.date = ‘2020-06-28’
  AND  o.order_id NOT IN (SELECT order_id 
                           FROM invoice i 
                           WHERE i.order_id=o.order_id )

If you want to improve your skills in the use of INSERT, UPDATE, and DELETE statements, I suggest the course How to INSERT, UPDATE, and DELETE Data in SQL where you can see several examples of these statements.

Second Stop: Subqueries in UPDATE Statements

Like SELECT, the UPDATE statement can have a subquery in several places or clauses. In an UPDATE, the two clauses in which subqueries are used most commonly are SET and WHERE.

The SET clause is where we define the new value for the column being modified by the UPDATE. We can use a subquery to obtain this new value, which can be selected from any table or any valid subquery, as long as we return only one record with only one column for each record being updated. The data type of the column returned by the subquery must be of the same type as that of the column being modified.

Let’s create an UPDATE to maintain our wine bottle stock up to date. We will run an UPDATE at the end of the day to reflect what we sold today. The code will look like this:

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

If we don’t use a WHERE clause in the UPDATE, we would end up modifying all the records in the table wine, including the records for the wines we did not sell today. The subquery returns a NULL for any wine not sold today, and we would erroneously SET the stock column to NULL, since the result of the expression “stock - NULL” is NULL. We need to fix this.

There are two approaches for the fix. The first is to modify the expression SUM(quantity) to return a zero instead of a NULL. For this, we simply need to use the COALESCE function, like this:

UPDATE wine w
SET stock = stock - (
                     SELECT coalesce(SUM (quantity), 0)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

The second approach is to add a subquery in the WHERE clause to modify only the wines that were ordered today and maintain the expression SUM(quantity) as is. The following query shows this approach:

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )
WHERE w.name IN (SELECT order.wine_name FROM order WHERE date = CURRENT_DATE)

This UPDATE is not ideal: this SET clause uses a correlated subquery. A correlated subquery is one that the database needs to execute many times—one time for each row being modified in the table. In our example, the query obtains the SUM(quantity) for each wine sold today. While correlated subqueries can be powerful, they are best avoided whenever possible as a matter of best practice. Here, we can avoid the correlated subquery by using a FROM clause in the UPDATE, as we can see below:

UPDATE wine w
SET stock = stock - subquery.total_in_orders
FROM ( SELECT wine_name, sum( order_quantity ) AS total_in_orders  
       FROM   order 
       WHERE  date = TODAY 
       GROUP BY wine_name 
     ) subquery
WHERE w.name = subquery.wine_name 

As a supplementary resource, I suggest the article Beginners Guide to the SQL Subquery, where you can learn the ABC of subqueries through examples of varying complexities.

Third Stop: Subqueries in DELETE Statements

With the DELETE statement, subqueries can be used only inside a WHERE clause. Let’s say we want to eliminate the records of wines for which we received no order in the last 6 months. We can create a subquery that returns the wines sold in the last 6 months, then identify the records we want to remove in the wine table by using a NOT IN operator. Let’s see how SQL does this:

DELETE FROM wine w
WHERE name NOT IN ( SELECT wine_name 
                    FROM   order 
                    WHERE  date >= CURRENT_DATE - interval ‘6 Months’ 
                  )

Now, suppose we want to eliminate the wines for which the total orders in the last 6 months were fewer than 10 units. The DELETE statement will look like this:

DELETE FROM wine w
WHERE 10 > ( SELECT SUM(quantity) 
                    FROM   order o 
                    WHERE  o.wine_name = w.name 
                      AND  date >= CURRENT_DATE - interval ‘6 Months’
                  )

Here, the subquery returns the quantity of bottles ordered in the last 6 months for each wine. By comparing this quantity to 10, we can determine whether a given wine should be eliminated.

Arriving at the End of the Subqueries Tour

Subqueries are like building blocks in SQL. We saw how they can be used in a variety of places, such as in SELECT statements or in any modification statements like INSERT, UPDATE, and DELETE. We saw how we could use a subquery in different clauses and that there were different ways to use subqueries in INSERT, UPDATE, and DELETE statements. If you want to learn more about subqueries, I suggest the article Subquery vs. CTE: A SQL Primer, in which a type of subquery called CTE is explained with a lot of examples and details. Finally, there are two courses with a lot of useful information on subqueries with plenty of examples: SQL Basics where you find a complete section on subqueries, and How to INSERT, UPDATE, and DELETE Data in SQL which is a more advanced course. Let’s go improve your SQL skills!