The DELETE statement is used to remove one or more rows from a table in Oracle Database. It is a Data Manipulation Language (DML) statement that can be rolled back if executed within a transaction.

This table and sample data is used in the examples below:

CREATE TABLE table_delete
(
id1 NUMBER(2,0)
, desc1 VARCHAR2(10)
);

INSERT INTO table_delete (id1, desc1) VALUES (1, 'A');
INSERT INTO table_delete (id1, desc1) VALUES (2, 'B');
INSERT INTO table_delete (id1, desc1) VALUES (3, 'C');
INSERT INTO table_delete (id1, desc1) VALUES (4, 'D');
INSERT INTO table_delete (id1, desc1) VALUES (5, 'E');
COMMIT;

Initial table contents:

id1   desc1
----- -----
1     A
2     B
3     C
4     D
5     E

The simplest form of DELETE removes all rows from a table. Use this with extreme caution as it will remove all data from the table.

Syntax:

DELETE FROM table_name;

To delete all rows from our example table:

DELETE FROM table_delete;

This statement would remove all rows from the table_delete table.

Most DELETE operations include a WHERE clause to specify which rows to remove. This is the most common and safest approach to deleting data.

Syntax:

DELETE FROM table_name WHERE condition;

Delete a single row based on a specific condition:

DELETE FROM table_delete WHERE id1 = 1;

Delete multiple rows using comparison operators:

DELETE FROM table_delete WHERE id1 > 3;

Select data from the table to see the result:

SELECT id1, desc1 FROM table_delete ORDER BY id1;

Query result:

id1 desc1
----- -----
2 B
3 C

DELETE statements can use subqueries in the WHERE clause to determine which rows to delete based on data from the same table or other tables.

Using a subquery with IN operator:

DELETE FROM table_delete WHERE id1 IN (SELECT id1 FROM table_delete WHERE id1 BETWEEN 1 AND 2);

Using a correlated subquery:

DELETE FROM table_delete t1
WHERE EXISTS (SELECT 1 FROM table_delete t2 WHERE t2.id1 = t1.id1 AND t2.desc1 = 'B');

Select data from the table:

SELECT id1, desc1 FROM table_delete ORDER BY id1;

Query result:

id1 desc1
----- -----
3 C
4 D
5 E