Deleting Rows from Multiple Tables

DELETE book, loan    # tables to delete from FROM book, loan    # tables to use in WHERE clause WHERE book.book_id = loan.book_id AND book.cond = 'poor'; 

A more complex form of the DELETE query allows you to delete rows from multiple tables in one operation. In the phrasebox, you are deleting any books that are in poor condition, along with any loans of those books.

The syntax for this kind of DELETE is as follows:

• DELETE Indicates the start of a DELETE query and should be immediately followed by a list of tables.

• list, of, tables, ... Immediately following the DELETE keyword is a list of one or more tables. Only tables listed here will have rows deleted from them.

• FROM Used to mark which tables should be a part of the WHERE clause of the query.

• list, of, tables, ... Only tables contained in this list may be used in the WHERE clause of the query.

• WHERE A standard WHERE clause. Rows that match conditions in the WHERE clause and that are in tables listed immediately after the DELETE keyword will be deleted. For more information on WHERE clauses, see Chapter 4.