Deleting Data in Oracle SQL

Key Concepts

Deleting data in Oracle SQL involves removing rows from a table. This operation is performed using the DELETE statement. Understanding the following key concepts is essential for effectively deleting data:

1. DELETE Statement

The DELETE statement is used to remove one or more rows from a table. It can be used with or without a WHERE clause. When used without a WHERE clause, it deletes all rows from the table. When used with a WHERE clause, it deletes only the rows that meet the specified condition.

2. WHERE Clause

The WHERE clause is used to specify the condition that rows must meet to be deleted. It allows for precise control over which rows are removed. Without a WHERE clause, all rows in the table are deleted.

3. TRUNCATE TABLE

The TRUNCATE TABLE statement is a faster alternative to the DELETE statement for removing all rows from a table. Unlike DELETE, it does not generate individual row delete operations and cannot be rolled back. It is useful for quickly clearing large tables.

4. Cascading Deletes

Cascading deletes are used when deleting a row from a parent table also deletes related rows in a child table. This is achieved by defining a ON DELETE CASCADE constraint when creating the foreign key relationship.

Detailed Explanation

1. DELETE Statement

The basic syntax for the DELETE statement is:


    DELETE FROM table_name WHERE condition;

If the WHERE clause is omitted, all rows in the table are deleted.

Example: Deleting a specific employee from the 'Employees' table:


        DELETE FROM Employees WHERE EmployeeID = 101;

2. WHERE Clause

The WHERE clause allows you to specify conditions that rows must meet to be deleted. This is useful for targeting specific rows without affecting others.

Example: Deleting all employees who were hired before a specific date:


        DELETE FROM Employees WHERE HireDate < TO_DATE('2020-01-01', 'YYYY-MM-DD');

3. TRUNCATE TABLE

The TRUNCATE TABLE statement is used to remove all rows from a table quickly. It is faster than the DELETE statement because it does not generate individual row delete operations.

Example: Truncating the 'Employees' table to remove all rows:


        TRUNCATE TABLE Employees;

4. Cascading Deletes

Cascading deletes ensure that when a row in a parent table is deleted, all related rows in a child table are also deleted. This is useful for maintaining referential integrity.

Example: Creating a foreign key with cascading delete:


        CREATE TABLE Orders (
            OrderID NUMBER PRIMARY KEY,
            EmployeeID NUMBER,
            FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID) ON DELETE CASCADE
        );

Now, deleting an employee will also delete all their orders.