DELETE Example

Suppose that a complex corporate deal results in the acquisition of all the Bay City (formerly Oakland) authors and their books by another publisher. You need to remove all these books from the titles table right away, but you don't know their titles or identification numbers. The only information you have is the authors' names and addresses.

You can delete the rows in the titles table by finding the author identification numbers for the rows that have San Francisco as the city in the authors table, and then 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 you want 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 must perform separate deletes to remove relevant rows in tables other than titles.

Here's 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 = 'San Francisco'

Note The primary key/foreign key relationship between titleauthor and authors/titles in the pubs database prevents you from actually performing this deletion because the foreign key won't allow you to delete any titles that have sales recorded in the sales table. To run this example, the referenced sales data must first be deleted.

For details, see the DELETE statement in the Microsoft SQL Server Transact-SQL Reference.