Subqueries in UPDATE, DELETE, and INSERT Statements

Subqueries in UPDATE, DELETE, and INSERT Statements

Subqueries can be nested in UPDATE, DELETE, and INSERT statements, as well as in SELECT statements.

The following query doubles the price of all books published by New Moon Books. The query updates the titles table; its subquery references the publishers table.

UPDATE titles
SET price = price * 2
WHERE pub_id IN
   (SELECT pub_id
   FROM publishers
   WHERE pub_name = 'New Moon Books')

Here's an equivalent UPDATE statement using a join:

UPDATE titles
SET price = price * 2
FROM titles INNER JOIN publishers ON titles.pub_id = publishers.pub_id
   AND pub_name = 'New Moon Books'

You can remove all sales records of business books with this nested query:

DELETE sales
WHERE title_id IN
   (SELECT title_id
   FROM titles
   WHERE type = 'business')

Here's an equivalent DELETE statement using a join:

DELETE sales
FROM sales INNER JOIN titles ON sales.title_id = titles.title_id
   AND type = 'business'