A subquery is a powerful way to find the data you want to use for another query. They are often used in SELECT and UPDATE statements to make these queries more efficient and easier to maintain.
There are several different ways to use subqueries in UPDATE statements. Let’s take a look at each of them.
SET and Subquery
The first method we will look at is using a subquery in the SET clause of an UPDATE statement.
Let’s say we had a table of products that looked like this:
[table id=29 /]
It stores a few pieces of information about different products that a company sells.
Assume that the company decided to increase the price of the “Couch” product (product id 1). However, instead of setting a specific price, they want to make it 20% higher than the most expensive product they have.
To do this, we can use a subquery in the SET clause. We could use separate statements, but it’s easier to maintain using a single statement.
Our statement would look like this:
UPDATE product SET price = ( SELECT MAX(price) * 1.2 FROM product ) WHERE product_id = 1;
You can see that the SET clause includes a subquery, which finds the MAX value of the price column in the product table and multiplies it by 1.2 to add 20%. Finally, the WHERE clause is outside the subquery to only update the product_id of 1, as it applies to UPDATE rather than to the subquery.
This will result in the following change:
[table id=30 /]
SET and Correlated Subquery
Another way to use a subquery in an UPDATE statement is to use a correlated subquery.
It works in a similar way to the previous example. However, a correlated subquery is a subquery that refers to the outer statement and may be a part of an UPDATE statement.
Using the data from the previous example (the product table), the company wants to deactivate all the products that have not had an order placed for them. The data for this is stored in the order_line table.
If we wrote it as a correlated subquery, the query will look like this:
UPDATE product p SET active = ( SELECT CASE WHEN COUNT(*) > 0 THEN 'Y' ELSE 'N' END FROM order_line o WHERE o.product_id = p.product_id );
The subquery will perform a COUNT function using a CASE statement to determine if the returned value is Y or N depending on the value of COUNT. It is calculated for each product_id and matches the outer query.
This will result in the active column for some products set to Y and others set to N:
[table id=31 /]
WHERE Greater Than Subquery
It is possible to use a subquery in the WHERE clause as well. Just like in the previous examples, this can be done to remove the separate step of finding a value to be updated and then to run the query to update it.
We can continue working with our example from the previous steps. Assume that the company wants to activate products that have a price above average. To do this, we can add a subquery to the WHERE clause.
First, deactivate all products.
UPDATE product SET active = ’N’;
Then, update the table using our subquery.
UPDATE product SET active = 'Y' WHERE price > ( SELECT AVG(price) FROM product );
This will set the active value to Y for all records that have a price above average.
The table now looks like this:
[table id=32 /]
It shows 2 records with an active value of Y because they are above average.
This kind of query can also be run with other operators that allow a single value, such as < or >=.
WHERE IN Subquery
Also, we can use a subquery with an IN operator in the WHERE clause.
This is similar to the previous example that used the greater than operator for a single value. The IN operator can be applied to multiple values.
Let’s say that the company wanted to update the price of some products that were the only item in the category. The prices would need to be halved.
Our query could look like this:
UPDATE product SET price = price / 2 WHERE category_id IN ( SELECT category_id FROM product GROUP BY category_id HAVING COUNT(*) = 1 );
The subquery finds all the category_id values where COUNT is 1. We don’t need to have COUNT in the SELECT part of the subquery, however, if we do, the query will display an error.
The UPDATE statement will update the price where the category meets the criteria of the subquery.
Our results will then look like this:
[table id=33 /]
The data looks very similar. However, the product with a category ID of 1 has had its price updated to half of its original cost, because it is the only product in its category.
UPDATE Subquery
Finally, you can use a subquery in an UPDATE statement for the table to be updated.
In the previous examples, we have just used the product table. However, you can use a subquery instead of the product table, which will return a result set that can be updated.
The result set needs to be updateable, similar to the case when you create a VIEW object and try to update it. It needs to be simple and have the primary key.
Thus, using our previous examples, suppose that the company wants to change the category for all products that are in category 4 to category 5.
Our query could look like this:
UPDATE ( SELECT product_id, category_id FROM product) SET category_id = 5 WHERE category_id = 4;
It’s a simple example that demonstrates the concept. The table has been replaced with the SELECT statement that only shows two columns of the table.
The results of this query would be:
[table id=34 /]
The same result could be received by moving the WHERE clause to the UPDATE statement:
UPDATE ( SELECT product_id, category_id FROM product WHERE category_id = 4) SET category_id = 5;
Conclusion
Using a subquery in an UPDATE statement can be a good way to improve the maintainability of your queries. It can also reduce the number of steps required to update your data by compressing two or more queries into a single query.
Tags: oracle, sql Last modified: July 15, 2022
