Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

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.

SQL

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

SQL VARIANTS An equivalent UPDATE statement using a ...