Combine Update and Delete with Subqueries in SQL

This post will show you how to:

  • update a row in a table with a where condition that uses another table.
  • delete a row with a where condition that uses another table.

Test data:

We are going to use the same example as in the post about joining without joins.
The first table is called products. It contains a list of products:

IDproduct_nameproduct_price
1white table1034
2red umbrella1009
3white armchair980

The second table is called sales. One row in this table corresponds to the sales of a product on a given day:

IDproduct_idquantitysales_date
111002014-10-05
21982014-10-06
32232014-10-04
43892014-10-03
53452014-10-05

First task:

We want to update the products' price. The update is executed on a product only if it happened at least once that this product was sold more than 80 times in a day.

The syntax is the following:

update products
set product_price = product_price + 2
where id in
(

select distinct product_id from sales
where quantity > 80
)

Note that we used the keyword “in”. After this keyword, we have a query in parenthesis returning a list of numbers (product_id). Every row in table products for which the column “id” is matching one of these numbers will be updated.

Here is the state of the table products after the update: the products with ID 1 and 3 were updated.

IDproduct_nameproduct_price
1white table1036
2red umbrella1009
3white armchair982

Explanations:

There are two rows in the sales table corresponding to days where Product ID 1 was sold more than 80 times:

IDproduct_idquantitysales_date
111002014-10-05
21982014-10-06

There is one row in the sales table corresponding to days where Product ID 3 was sold more than 80 times:

IDproduct_idquantitysales_date
43892014-10-03

There is no such row for product id equal to 2.

Second task:

We want to update the product price. The update is executed on a product only if it happened at least twice that this product was sold more than 80 times in a day.

The syntax is the following:

update products p
set p.product_price = p.product_price + 1
where
(
select count(*) from sales s
where s.product_id = p.id
and s.quantity > 80
) >= 2

Note that in the where condition, instead of having a column name that we compare with a number, we have a query in parenthesis returning a number:

(
select count(*) from sales s
where s.product_id = p.id
and s.quantity > 80
)

This query is using an aliases to the product table (p) and an alias to the sales table (s). Aliases are used to match each row in the table products to one or more rows in the table sales.

The number returned by this query is compared to 2. Each row in products for which this query is returning a number greater or equal to 2 will be updated.

Here is the state of the table products after the update: the products with ID 1 only was updated.

IDproduct_nameproduct_price
1white table1037
2red umbrella1009
3white armchair980

Product with ID 1 is the only product for which the number of rows returned from the subquery is equal to or greater than 2:

IDproduct_idquantitysales_date
111002014-10-05
21982014-10-06

Third task: DELETE

We will now delete rows that fulfil the same condition as for the update described above. In the case of the delete, we have to use the full table names instead of the aliases:

delete from products
where
(
select count(*) from sales
where sales.product_id = products.id
and sales.quantity > 80
) >= 2

Here is the state of the table products after the update: the row corresponding to product ID 1 was deleted.

IDproduct_nameproduct_price
2red umbrella1009
3white armchair982

Previous Post

Copy Data from one Table to another in SQL.

 
Please help this site survive, share on:



Author:
Last udpated: September 10, 2017
Created: October 20, 2014

Something not clear? Ask your question here:

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>