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 Transact-SQL that allows you to select data from one or more tables and delete corresponding data from the first-named table. The rows you select in the from clause specify the conditions for the delete command.
Suppose that a complex corporate deal results in the acquisition of all the Oakland authors and their books by another publisher. You must immediately remove all these books from the titles table, 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 Oakland 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. To remove relevant rows in tables other than titles, use separate delete statements.
This is the correct statement:
delete titles from authors, titles, titleauthor where titles.title_id = titleauthor.title_id and authors.au_id = titleauthor.au_id and city = "Oakland"
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.
