Oracle DELETE Statement with Examples

In this article, we will learn about the Oracle DELETE statement which is used for deleting values.

Oracle DELETE statement is used for deleting values from from one or more rows or columns within a table.

Syntax:

The syntax of Oracle DELETE statement is as follows:

DELETE
FROM
    table_name
WHERE
    condition;

Here,

table_name : This is the table name from which we want to delete values

condition : This is used for specifying the WHERE condition which tells Oracle which values to delete specifically. If we don’t specify this WHERE condition then Oracle will delete all rows within the table.

TRUNCATE TABLE is a much faster alternative when we need to delete all rows from the table.

ORACLE DELETE Examples

Let’s create a sample table for these examples:

CREATE TABLE location_site AS
SELECT
    location_id,
    postal_code,
    city,
    state_province,
    country_id,
    country_name
FROM locations
INNER JOIN countries USING(country_id);

This query created a table named location_site by copying data from two tables: Locations and Countries. We used INNER JOIN to both these tables.

Let’s have a look at the table:

1. Oracle DELETE – Deleting one row from a table

We use the following query for deleting the row having location_id =1100 and country_id=IT:

DELETE
FROM
    location_site
WHERE
    location_id = 1100
    AND country_id = IT;

Result:

As we can see, the row with location_id = 1100 has been deleted.

2. Oracle DELETE – Deleting multiple rows from a table

We use the following query to delete all rows with country_id= ‘US’:

DELETE
FROM
    location_site
WHERE
    cpuntry_id = 'US';

Result:

All rows with country_id = ‘US’ have been deleted.

3. Oracle DELETE – Deleting all rows from a table

The following query is used for deleting all rows from table:

DELETE FROM location_site;

4. Oracle DELETE – DELETE Cascade

Often we might across a situation where we want to delete a row from table which is having a foreign key relationship with rows from other tables:

Let’s say for example, we might want to delete country_id from Locations table and we might also want to delete all the rows having the same country_id from countries tables.

For executing this we will have to write two separate queries:

DELETE
FROM
    locations
WHERE
    country_id = 'US';

DELETE
FROM
    countries
WHERE
    country_id = 'US';

COMMIT;

Here, COMMIT WORK statement is used for ensuring both DELETE statements are successfully executed while preventing orphaned rows in case the second statement fails.

This is condition where setting up the constraints right at the time of creating a new table might serve handy:

CREATE TABLE countries 
(
    country_id   NUMBER( 12, 0 )                                , 
    -- other columns
    -- ...
    CONSTRAINT fk_locations_countries 
    FOREIGN KEY( country_id ) 
    REFERENCES locations( country_id ) 
    ON DELETE CASCADE
);

By doing this, whenever we delete a row from countries table,

DELETE
FROM
    countries
WHERE
    country_id = 'US';

All rows from locations, where country_id =’US’ are also deleted automatically.

5. Use of Sub-Query in DELETE statement

Let’s create data from the table we created using data from other table:

DELETE FROM location_site
WHERE country_id =
(
SELECT country_id FROM locations
WHERE city ='Venice'
);

Result:

This query deleted all the rows from location_site table where country =’Italy’. Here, we used data from other table for deleting rows in this table.

In this article, we learned how to use DELETE statement in Oracle under different scenarios.