Oracle/PLSQL: DELETE Statement

SQL: DELETE Statement

The SQL DELETE statement allows you to delete a single record or multiple records from a table.

The syntax for the SQL DELETE statement is:

DELETE FROM table

WHERE predicates;

SQL DELETE Statement - One condition example

Let's take a look at a simple example, where we just have one condition in our SQL DELETE statement:

DELETE FROM suppliers

WHERE supplier_name = 'IBM';

This SQL DELETE statement would delete all records from the suppliers table where the supplier_name is IBM.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following SQL SELECT statementbefore performing the delete.

SELECT count(*)

FROM suppliers

WHERE supplier_name = 'IBM';

SQL DELETE Statement - Using SQL EXISTS Clause example

You can also perform more complicated deletes.

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 SQL FROM clause when you are performing a delete, you can use the SQL 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 SQL DELETE statement 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.

If you wish to determine the number of rows that will be deleted, you can run the following SQL SELECT statement before performing the delete.

SELECT count(*) FROM suppliers

WHERE EXISTS

  ( select customers.name

    from customers

    where customers.customer_id = suppliers.supplier_id

    and customers.customer_name = 'IBM' );

Frequently Asked Questions

---

Question: How would I write an SQL DELETE statement to delete all records in TableA whose data in field1 & field2 DO NOT match the data in fieldx & fieldz of TableB?

Answer: You could try something like this for your SQL DELETE statement:

DELETE FROM TableA

WHERE NOT EXISTS

  ( select *

    from TableB

     where TableA.field1 = TableB.fieldx

     and TableA.field2 = TableB.fieldz );