Chapter 5: Subqueries: Using Queries Within Other Queries

Subqueries in update, delete, and insert statements

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

Running the sample queries in this section changes the pubs2 database. Ask a System Administrator to help you get a clean copy of the sample 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"

You can 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"

View this book as PDF