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.

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.


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.
WHERE  person_id IN
  (SELECT id
   FROM   person
   WHERE  place_of_birth = 'San Francisco'

-- It's only a test. Restore the rows.

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 overcame such situations there are different strategies:

  • Delete all dependent rows prior to the intended row.
  • Define the Foreing Key constraint as DEFERRED (it will be check not before COMMIT) and delete the depending rows before or after the intended one.
  • Define the Foreing Key constraint as CASCADE. In this case the depending rows will be deleted automatically.


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.

  This article uses material from the Wikipedia page available here. It is released under the Creative Commons Attribution-Share-Alike License 3.0.



Connect with defaultLogic
What We've Done
Led Digital Marketing Efforts of Top 500 e-Retailers.
Worked with Top Brands at Leading Agencies.
Successfully Managed Over $50 million in Digital Ad Spend.
Developed Strategies and Processes that Enabled Brands to Grow During an Economic Downturn.
Taught Advanced Internet Marketing Strategies at the graduate level.

Manage research, learning and skills at defaultLogic. Create an account using LinkedIn or facebook to manage and organize your Digital Marketing and Technology knowledge. defaultLogic works like a shopping cart for information -- helping you to save, discuss and share.

  Contact Us