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.

Introduction to the Oracle DELETE statement #

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, provide the name of the table from which you want to delete data.
• Second, specify which row you want to delete using the condition in the WHERE clause. If you omit the WHERE clause, the DELETE statement deletes 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 Statement examples #

Let’s create a new table called 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)

Try it

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

Deleting one row from a table #

The following statement uses the DELETE statement to delete a row where the 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)

Try it

Oracle returned the following message:

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

Deleting multiple rows from a table #

The following statement uses the DELETE statement to delete rows with the order id 1:

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

Try it

Oracle returned the following message:

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

Deleting all rows from a table #

The following example uses the DELETE statement to delete all rows from the sales table:

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

Try it

And we got 652 rows deleted.

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

Delete with cascade #

In practice, you often delete a row from a table that 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 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 a “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 set up the foreign key 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)

It will also automatically delete all rows with order id is 1 from the order_items table.

Summary #

• Use the DELETE statement to delete one or more rows from a table.
• Use the DELETE statement with a WHERE clause to filter rows for deletion.