Structured Query Language/DELETE 2

Hint: Be carefull and deactivate AUTOCOMMIT.

Because the DELETE command deletes rows as a whole and not partly, the syntax is straightforward. Its structure was shown on a previous page. The page on hand offers only one addition: The WHERE clause isn't limited to simple conditions like 'id = 10' but may contain a subquery. This gives the command much more flexibility.

The use of subqueries as part of a DELETE command is identical to its use within an UPDATE or SELECT command.

There is another command for the deletion of rows. The TRUNCATE command is very similar to DELETE. TRUNCATE deletes all rows of a table and shows better performance. But it has no mechanism to choose individual rows.

Example
The example command deletes contact information from persons who are born in San Francisco.

Correlated subqueries in combination with DELETE commands, are not supported by all implementations.

It often happens that the DBMS rejects DELETE commands because Foreign Key constraints will be violated during its execution. E.g.: if the command tries to delete a person to whom a contact or hobby information is known, the command fails (as a whole). To overcome such situations, there are different strategies:
 * Delete all dependent rows prior to the intended row.
 * Define the Foreign Key constraint as DEFERRED (it will be check not before COMMIT) and delete the depending rows before or after the intended one.
 * Define the Foreign Key constraint as CASCADE. In this case, the depending rows will be deleted automatically.

Exercise
Delete hobby information for family Goldstein.