Oracle / PLSQL: DELETE Statement

This Oracle tutorial explains how to use the Oracle DELETE statement with syntax, examples, and practice exercises.

Description

The Oracle DELETE statement is used to delete a single record or multiple records from a table in Oracle.

Syntax

The syntax for the DELETE statement in Oracle/PLSQL is:

DELETE FROM table
[WHERE conditions];

Parameters or Arguments

table

The table that you wish to delete records from.

WHERE conditions

Optional. The conditions that must be met for the records to be deleted. If no conditions are provided, then all records from the table will be deleted.

Note

• You do not need to list fields in the Oracle DELETE statement since you are deleting the entire row from the table.

Example - Using One condition

Let's look at a simple Oracle DELETE query example, where we just have one condition in the DELETE statement.

For example:

DELETE FROM customers
WHERE last_name = 'Smith';

This Oracle DELETE example would delete all records from the customers table where the last_name is Smith.

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 Oracle SELECT statement before performing the delete.

SELECT count(*)
FROM customers
WHERE last_name = 'Smith';

Example - Using Two conditions

Let's look at an Oracle DELETE example, where we just have two conditions in the DELETE statement.

For example:

DELETE FROM customers
WHERE last_name = 'James'
AND customer_id > 25;

This Oracle DELETE example would delete all records from the customers table where the last_name is 'James' and the customer_id is greater than 25.

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 Oracle SELECT statement before performing the delete.

SELECT count(*)
FROM customers
WHERE last_name = 'James'
AND customer_id > 25;

Example - Using EXISTS Clause

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

For example:

DELETE FROM suppliers
WHERE EXISTS
  ( SELECT customers.customer_name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customer_id > 25 );

This Oracle DELETE example would delete all records in the suppliers table where there is a record in the customers table whose customer_id is greater than 25, and the customer_id matches the supplier_id.

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

SELECT COUNT(*) FROM suppliers
WHERE EXISTS
  ( SELECT customers.customer_name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customer_id > 25 );

Frequently Asked Questions

Question: How would I write an Oracle 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 Oracle DELETE statement:

DELETE FROM TableA
WHERE NOT EXISTS
  ( SELECT *
    FROM TableB
     WHERE TableA.field1 = TableB.fieldx
     AND TableA.field2 = TableB.fieldz );

Practice Exercise #1:

Based on the contacts table, delete all records from the contacts table who reside in the City of 'Las Vegas' and whose first_name is 'Jane'.

CREATE TABLE contacts
( contact_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(2),
  zip_code varchar2(10),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

Solution for Practice Exercise #1:

The following Oracle DELETE statement would delete these records from the contacts table:

DELETE FROM contacts
WHERE city = 'Las Vegas'
AND first_name = 'Jane';

Practice Exercise #2:

Based on the contacts table, delete all records from the contacts table whose contact_id is greater than or equal to 5000 and less than 6000.

CREATE TABLE contacts
( contact_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(2),
  zip_code varchar2(10),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

Solution for Practice Exercise #2:

The following Oracle DELETE statement would delete these records from the contacts table:

DELETE FROM contacts
WHERE contact_id >= 5000
AND contact_id < 6000.

Or you could write the solution using the BETWEEN clause as follows:

DELETE FROM contacts
WHERE contact_id BETWEEN 5000 AND 5999;