Subqueries in UPDATE, DELETE, and INSERT Statements

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

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. If you are following along, use transaction control statements to roll back changes to the data. On the demo system, start with a COMMIT. Run the UPDATE, DELETE, or INSERT, and check your data with a SELECT. Then execute a ROLLBACK command to cancel the changes.

update titles
set price = price * 2
where pub_id in
  (select pub_id
					   from publishers
					   where pub_name = 'New Age Books')
				

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 orders for business books with this nested SELECT statement:

delete salesdetails
where title_id in
  (select title_id
   from titles
   where type = 'business')

delete salesdetails
from salesdetails, titles
where salesdetails.title_id = titles.title_id
  and type = 'business'