Back to articles list July 30, 2020 - 9 minutes read How to Use Subqueries in INSERT, UPDATE, and DELETE Statements Ignacio L. Bisso Ignacio is a database consultant from Buenos Aires, Argentina. He’s worked for 15 years as a database consultant for IT companies like Informix and IBM. These days, he teaches databases at Sarmiento University and works as a PostgreSQL independent SQL consultant. A proud father of four kids with 54 years in his backpack, Ignacio plays soccer every Saturday afternoon, enjoying every match as if it’s his last one. Tags: sql learn sql subquery 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! 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 NameTypeStockPriceWineCellar BrilliantChardonnay100022SkyWine BleuBlendCabernet98018LeBleu CatedralMalbec10027SantoRojo SantiagoMalbec204024Wines of Chile West SideCabernet140034Napa Wines Oro RossoCabernet75031Italian Caves High CoastChardonnay256017De la Costa wines order Order_idDateClient_idWine_namequantity 1Jan 10 2020100Catedral50 2Feb 15 2020103Santiago230 3Mar 12 2020102West Side85 4Mar 30 2020100Oro Rosso150 5May 3 2020100Oro Rosso30 6Jun 28 2020103Santiago200 7Jun 28 2020102West Side150 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: Invoice_idDateClient_idAmountDescriptionOrder_id 12020-01-10100$13550 bottles of Catedral1 22020-02-15103$5520230 bottles of Santiago2 32020-03-12102$289085 bottles of West Side3 42020-03-30100$4650150 bottles of Oro Rosso4 52020-05-03100$93030 bottles of Oro Rosso5 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: DateClient_idAmountDescriptionOrder_id 2020-06-28103$4800200 bottles of Santiago6 2020-06-28102$5100150 bottles of West Side7 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: Invoice_idDateClient_idAmountDescriptionOrder_id 12020-01-10100$13550 bottles of Catedral1 22020-02-15103$5520230 bottles of Santiago2 32020-03-12102$289085 bottles of West Side3 42020-03-30100$4650150 bottles of Oro Rosso4 52020-05-03100$93030 bottles of Oro Rosso5 62020-06-28103$4800200 bottles of Santiago6 72020-06-28102$5100150 bottles of West Side7 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! Tags: sql learn sql subquery You may also like Learn to Write a SQL Correlated Subquery in 5 Minutes A correlated subquery is a subquery that is executed many times—once for each record (row) returned by the outer (main) query. Sounds complex? Not! Read more What Is a Nested Query in SQL? Have you ever faced a problem where it would be great to put a SELECT inside another SELECT statement? Learn how to use a nested query in SQL. Read more SQL Subqueries The article describes what a subquery is and what these useful statements look like. We will cover basic examples with the IN, EXISTS, ANY, and ALL operators, look at subqueries in FROM and WHERE clauses, and explore the difference between correlated and nested subqueries. Read more Beginner’s Guide to the SQL Subquery Did you know that you can use a SQL query inside another SQL query? Find out the ABCs of subqueries and improve your SQL skills! Read more Correlated Subquery in SQL: A Beginner’s Guide Correlated subqueries are the only way to solve some SQL statements. But they can be very slow. In this post, we’ll talk about why, how, and when to use them. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.