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, 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 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 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 is 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.