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'