Growing by Simon Peckham

Delete From One Table Whose Values Don’t Appear in Another Table

Sometimes you will find that you have items in a table whose values reference items in another table that no longer exist.  For example in ATG you may have orders that reference profiles that no longer exist.  This could happen if an order is for an anonymous profile that was deleted.

Here is an example of how to delete items in a table whose values reference items in another table that no longer exist, in this case ATG orders whose profiles no longer exist.

DELETE FROM dcspp_order
WHERE profile_id
NOT IN
(
  SELECT id
  FROM dps_user
);

This example does not actually work because of the dependencies on the dcspp_order table so please don’t try it.

Delete From One Table Based on Values in Another Table

Sometimes you want to delete items in one tables based on values in another table.  You can do this similarly to the previous case.

DELETE FROM dcspp_order
WHERE profile_id
IN
(
  SELECT id
  FROM dps_user
  WHERE id LIKE '6%'
);

This example also does not actually work because of the dependencies on the dcspp_order table so please don’t try it.

For further reading please see How to delete records from a SQL Server database and SQL Delete Rows Based on Another Table.