Using DELETE Statement in Oracle

This article is written about how to use the Oracle DELETE announcement with syntax, examples, and practice exercises.

Description

The Oracle DELETE statement is used to delete a single file or more than one documents from a table in Oracle.

Syntax

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

DELETE FROM table
[WHERE conditions];

Parameters or Arguments

table The table that you desire to delete data from. WHERE conditions Optional. The stipulations that ought to be met for the documents to be deleted. If no conditions are provided, then all documents from the desk will be deleted.

Note

You do now not need to list fields in the Oracle DELETE declaration because you are deleting the entire row from the table.

Example – Using One condition

Let’s seem to be at a easy Oracle DELETE query example, where we simply have one situation in the DELETE statement.

For example:

DELETE FROM customers
WHERE last_name = 'Smith';

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

You can also want to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by way of walking the following Oracle SELECT announcement earlier than performing the delete.

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

Example – Using Two conditions

Let’s seem to be at an Oracle DELETE example, where we simply have two conditions in the DELETE statement.

For example:

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

This Oracle DELETE example would delete all information from the clients table where the last_name is ‘Anderson’ and the customer_id is greater than 25.

You can also want to test for the wide variety of rows that will be deleted. You can determine the wide variety of rows that will be deleted by means of jogging the following Oracle SELECT assertion earlier than performing the delete.

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

Example – Using EXISTS Clause

You can also perform more complicated deletes.

You may desire to delete archives in one desk based on values in any other table. Since you cannot listing more than one desk 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 archives in the suppliers desk the place there is a file in the clients desk whose customer_id is greater than 25, and the customer_id suits the supplier_id.

If you want to decide the number of rows that will be deleted, you can run the following Oracle SELECT declaration earlier than 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 statistics in field1 & field2 DO NOT in shape the data in fieldx & fieldz of TableB?

Answer: You should strive 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 documents from the contacts table who dwell 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 documents 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 documents from the contacts table whose contact_id is larger than or equal to 5000 and much 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 announcement would delete these files from the contacts table:

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

Or you should write the answer the usage of the BETWEEN clause as follows:

DELETE FROM contacts
WHERE contact_id BETWEEN 5000 AND 5999;