DELETE based on a JOIN condition

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the FROM clause when you are performing a delete, you can use the EXISTS clause.

For example:

DELETE FROM suppliers
    WHERE EXISTS
      ( select customers.name
         from customers
         where customers.customer_id = suppliers.supplier_id
         and customers.customer_name = 'IBM' );

This would delete all records in the suppliers table where there is a record in the customers table whose name is IBM, and the customer_id is the same as the supplier_id.