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;
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
WHEREclause. If you omit the
WHEREclause, the Oracle
DELETEstatement 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.
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);
This statement created the
sales table and copied data from the
order_items tables in the sample database to the
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;
Oracle returned the following message:
1 row deleted.
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;
And Oracle returned the following message:
12 rows deleted.
C) Oracle DELETE – delete all rows from a table
The following example deletes all rows from the
DELETE FROM sales;
And we got 625 rows deleted.
652 rows deleted.
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;
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 );
By doing this, whenever you delete a row from the
orders table, for example:
DELETE FROM orders WHERE order_id = 1;
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.