Subqueries in update, delete, and insert Statements

You can nest subqueries in update, delete, and insert statements as well as in select statements.

Note: Running the sample queries in this section changes the pubs2 database. If you require the original pubs2 database after you have run these queries, ask a system administrator to reload the pubs2 database.

The following query doubles the price of all books published by New Age 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 Age Books")

An equivalent update statement using a join is:

update titles 
set price = price * 2 
from titles, publishers 
where titles.pub_id = publishers.pub_id 
and pub_name = "New Age Books"

Remove all records of sales of business books with this nested select statement:

delete salesdetail 
where title_id in 
   (select title_id 
    from titles 
    where type = "business")

An equivalent delete statement using a join is:

delete salesdetail 
from salesdetail, titles 
where salesdetail.title_id = titles.title_id 
and type = "business"