Chapter 7: Adding, Changing, and Deleting Data

Using the from clause with delete

The optional from immediately after the delete keyword is included for compatibility with other versions of SQL. The from clause in the second position of a delete statement is a special Transact-SQL feature that allows you to select data from a table or tables and delete corresponding data from the first-named table. The rows you select in the from clause specify the conditions for the delete.

Suppose that a complex corporate deal results in the acquisition of all the College Town (formerly Oakland) authors and their books by another publisher. You must remove all these books from the titles table right away, but you do not know their titles or identification numbers. The only information you have is the author’s names and addresses.

You can delete the rows in titles by finding the author identification numbers for the rows that have Big Bad Bay City as the town in the authors table and using these numbers to find the title identification numbers of the books in the titleauthor table. In other words, a three-way join is required to find the rows to delete in the titles table.

The three tables are all included in the from clause of the delete statement. However, only the rows in the titles table that fulfill the conditions of the where clause are deleted. You would have to use separate delete statements to remove relevant rows in tables other than titles.

Here is the statement you need:

delete titles 
from authors, titles, titleauthor 
where titles.title_id = titleauthor.title_id 
and authors.au_id = titleauthor.au_id 
and city = "Big Bad Bay City"

The deltitle trigger in the pubs2 database prevents you from actually performing this deletion, because it does not allow you to delete any titles that have sales recorded in the sales table.

View this book as PDF