Oracle DELETE

Summary: in this tutorial, you will learn how to use the Oracle DELETE statement to delete one or more rows from a table.

To delete one or more rows from a table, you use the Oracle DELETE statement as follows:

DELETE FROM table_name WHERE condition;
Code language: SQL (Structured Query Language) (sql)

In this statement,

  • First, you specify the name of the table from which you want to delete data.
  • Second, you specify which row should be deleted by using the condition in the WHERE clause. If you omit the WHERE clause, the Oracle DELETE statement removes all rows from the table.

Note that it is faster and more efficient to use the TRUNCATE TABLE statement to delete all rows from a large table.

Oracle DELETE examples

Let’s create a new table named sales, which contains all sales order data, for the demonstration purpose:

CREATE TABLE sales AS SELECT order_id, item_id, product_id, quantity, unit_price, status, order_date, salesman_id FROM orders INNER JOIN order_items USING(order_id);
Code language: SQL (Structured Query Language) (sql)

This statement created the sales table and copied data from the orders and order_items tables in the sample database to the sales table.

A) Oracle DELETE – delete one row from a table

The following statement deletes a row whose order id is 1 and item id is 1:

DELETE FROM sales WHERE order_id = 1 AND item_id = 1;
Code language: SQL (Structured Query Language) (sql)

Oracle returned the following message:

1 row deleted.
Code language: SQL (Structured Query Language) (sql)

B) Oracle DELETE – delete multiple rows from a table

The following statement deletes all rows whose order id is 1:

DELETE FROM sales WHERE order_id = 1;
Code language: SQL (Structured Query Language) (sql)

And Oracle returned the following message:

12 rows deleted.
Code language: SQL (Structured Query Language) (sql)

C) Oracle DELETE – delete all rows from a table

The following example deletes all rows from the sales table:

DELETE FROM sales;
Code language: SQL (Structured Query Language) (sql)

And we got 625 rows deleted.

652 rows deleted.
Code language: SQL (Structured Query Language) (sql)

D) Oracle DELETE – delete cascade

In practice, you often delete a row from a table which has a foreign key relationship with rows from other tables.

For example, you want to delete the sales order with id 1 from the orders table and also delete all the line items associated with the order id 1 from the order_items table. Typically, you can think of issuing two DELETE statements as follows:

DELETE FROM orders WHERE order_id = 1; DELETE FROM order_items WHERE order_id = 1; COMMIT WORK;
Code language: SQL (Structured Query Language) (sql)

Note that the COMMIT WORK statement ensures both DELETE statements execute in all or nothing manner, which prevents the orphaned rows in the order_items table in case the second DELETE statement fails.

However, this is unnecessary if you know how to setup table’s constraint correctly.

In this case, when you create the order_items table, you define a foreign key constraint with the DELETE CASCADE option as follows:

CREATE TABLE order_items ( order_id NUMBER( 12, 0 ) , -- other columns -- ... CONSTRAINT fk_order_items_orders FOREIGN KEY( order_id ) REFERENCES orders( order_id ) ON DELETE CASCADE );
Code language: SQL (Structured Query Language) (sql)

By doing this, whenever you delete a row from the orders table, for example:

DELETE FROM orders WHERE order_id = 1;
Code language: SQL (Structured Query Language) (sql)

All the rows whose order id is 1 in the order_items table are also deleted automatically by the database system.

In this tutorial, you have learned how to use the Oracle DELETE statement to remove rows from a table.

Was this tutorial helpful?