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.

-- 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.

Exercise

Delete hobby information for family Goldstein.

Click to see solution

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;