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 very simple. 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.
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.
The example command deletes contact information from persons which are born in San Francisco.
-- Delete rows depending on a criteria which resides in a different table. DELETE FROM contact WHERE person_id IN (SELECT id FROM person WHERE place_of_birth = 'San Francisco' ); -- It's only a test. Restore the rows. ROLLBACK;
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.
Delete hobby information for family Goldstein.
DELETE FROM person_hobby WHERE person_id IN (SELECT id FROM person WHERE lastname = 'Goldstein' ); -- Refrain from deleting the hobby itself - because: -- a) The hobby may be allocated to a different person. -- b) After the information in person_hobby is deleted, there is no longer the possibility to get -- to old assignment between person and hobby. -- It's only a test. Restore the rows. ROLLBACK;