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 statement 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, publishers
WHERE titles.pub_id = publishers.pub_id
    AND pub_name = 'New Moon Books'

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

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, titles
WHERE sales.title_id = titles.title_id
    AND type = 'business'