5 Ways to Update Data with a Subquery in Oracle SQL

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:

PRODUCT_IDPRODUCT_NAMECATEGORY_IDPRICECOLORACTIVE
1Couch4199.95Grey Y
2Armchair445BlackY
3Rug279RedY
4Pot with Lid315BlueY
5TV Stand445BlackY
6Cushion28YellowY
7Speakers1250BlackY
8Stool419BlueN
9Ottoman425Brown Y
10Vase315WhiteY

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:

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:

PRODUCT_IDPRODUCT_NAMECATEGORY_IDPRICECOLORACTIVE
1Couch4300GreyY
2Armchair445Black Y
3Rug279RedY
4Pot with Lid315BlueY
5TV Stand445BlackY
6Cushion28YellowY
7Speakers1250BlackY
8Stool419BlueN
9Ottoman425BrownY
10Vase315WhiteY

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:

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:

PRODUCT_IDPRODUCT_NAMECATEGORY_IDPRICECOLORACTIVE
1Couch4300GreyY
2Armchair445BlackN
3Rug279RedY
4Pot with Lid315BlueY
5TV Stand445BlackN
6TV Stand28YellowN
7Speakers1250BlackN
8Stool419BlueN
9Ottoman425BrownY
10Vase315WhiteN

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.

Then, update the table using our subquery.

This will set the active value to Y for all records that have a price above average.

The table now looks like this:

PRODUCT_IDPRODUCT_NAMECATEGORY_IDPRICECOLORACTIVE
1Couch4300GreyY
2Armchair445BlackN
3Rug279RedN
4Pot with Lid315BlueN
5TV Stand445BlackN
6Cushion28YellowN
7Speakers1250BlackY
8Stool419BlueN
9Ottoman425BrownN
10Vase315WhiteN

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:

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:

PRODUCT_IDPRODUCT_NAMECATEGORY_IDPRICECOLORACTIVE
1Couch4300GreyY
2Armchair445BlackN
3Rug279RedN
4Pot with Lid315BlueN
5TV Stand445BlackN
6Cushion28YellowN
7Speakers1125BlackY
8Stool419BlueN
9Ottoman425BrownN
10Vase315WhiteN

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:

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:

PRODUCT_IDPRODUCT_NAMECATEGORY_IDPRICECOLORACTIVE
1Couch5300GreyY
2Armchair545BlackN
3Rug279RedN
4Pot with Lid315BlueN
5TV Stand545BlackN
6Cushion28YellowN
7Speakers1125BlackY
8Stool519BlueN
9Ottoman525BrownN
10Vase 315WhiteN

The same result could be received by moving the WHERE clause to the UPDATE statement:

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.

Ben Brumm

Ben Brumm

Ben Brumm is a software consultant with over 10 years experience in the IT industry. He is also the founder of DatabaseStar.com, a website that teaches database developers how to improve their skills and their career.
Ben Brumm

Latest posts by Ben Brumm (see all)

Ben Brumm

Ben Brumm is a software consultant with over 10 years experience in the IT industry. He is also the founder of DatabaseStar.com, a website that teaches database developers how to improve their skills and their career.