PL/SQL DELETE Statement

PL/SQL provides the DELETE statement to remove one or more records from a database table. It helps in maintaining data integrity by removing unwanted or outdated data.

• It deletes records from a specified table.
• It removes only rows that satisfy the WHERE condition.
• It deletes all rows if no condition is specified.
• It helps maintain clean and consistent data.

Syntax:

DELETE FROM table_name
WHERE condition;

Key terms:

• table_name: the name of the table from which we want to delete the records.
• condition: specifies which row needs to be deleted. If the condition is not used, all rows in table will deleted.

Employee Table

Firstly, create the employeeDetails table. Then, INSERT INTO is used to add rows with employee_id, name and department.

Query:

CREATE TABLE employeeDetails (
    employee_id NUMBER(10) PRIMARY KEY,
    name VARCHAR2(10),
    department VARCHAR2(20)
);

-- Insert a single row
INSERT INTO employeeDetails (employee_id, name, department)
VALUES (101, 'John Smith', 'HR');

-- Insert another row
INSERT INTO employeeDetails (employee_id, name, department)
VALUES (102, 'Jane Doe', 'IT');

-- Insert  a third row
INSERT INTO employeeDetails (employee_id, name, department)
VALUES (103, 'Mike Brown', 'Finance');

-- Insert a fourth row
INSERT INTO employeeDetails (employee_id, name, department)
VALUES (104, 'Lisa Wong', 'HR');

--Insert a fifth row
INSERT INTO employeeDetails (employee_id, name, department)
VALUES (105, 'Alice Grey', 'Marketing');

Output:

Department Table

Create another table using CREATE TABLE with the required columns.Then, use INSERT INTO to add rows with department and name.

Query:

CREATE TABLE department (
    department VARCHAR2(20), 
    name VARCHAR2(10)
);
--Insert a single row
INSERT INTO department (department, name)
VALUES ('HR', 'John Smith');

--Insert another row
INSERT INTO department (department, name)
VALUES ('Finance', 'Mike Brown');

--Insert third row
INSERT INTO department (department, name)
VALUES ('IT', 'Jane Doe');

Output:

Example 1: Using One Condition in the DELETE Statement

To delete employees from the employeeDetails table whose department is IT, use the DELETE statement with a condition on the department column.

Query:

DELETE FROM employeeDetails
WHERE department = 'IT';

Output:

• DELETE FROM employeeDetails deletes rows from the table.
• WHERE department = 'IT' removes only rows where department is IT.
• The IT employee (Jane Doe) is deleted and other rows remain unchanged.

Example 2: Using Two Conditions in the DELETE Statement

This example shows how to use DELETE with multiple conditions to remove specific rows from employeeDetails. For example, deleting an employee who works in HR and has employee_id = 101 using the AND operator.

Query:

DELETE FROM employeeDetails
WHERE department = 'HR' AND employee_id = 101;

Output:

• DELETE FROM employeeDetails deletes rows from the table.
• WHERE department = 'HR' AND employee_id = 101 removes only the matching row.
• Thus, John Smith (HR, 101) is deleted and other rows remain unchanged.

Example 3: Using NOT EXISTS Clause

The NOT EXISTS clause checks if no rows exist in a subquery. Using DELETE with a subquery helps remove rows based on missing related data. For example, it can delete employees whose departments no longer exist in the department table.

Query:

DELETE FROM employeeDetails e
WHERE NOT EXISTS (
    SELECT 1 FROM department d
    WHERE d.department = e.department
);

Output:

• DELETE FROM employeeDetails deletes rows from the table.
• WHERE NOT EXISTS deletes rows when no match is found in the department table.
• Thus, employees with non-existing departments (e.g., Alice Grey in Marketing) are deleted, while others (HR, IT, Finance) remain unchanged.