SQL DELETE Tutorial

Example Table

We will use the following table named employees for our examples:

        CREATE TABLE employees (
            employee_id INT PRIMARY KEY,
            employee_name VARCHAR(50),
            department VARCHAR(50),
            salary DECIMAL(10, 2)
        );
        
        INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
        (1, 'John Doe', 'HR', 50000.00),
        (2, 'Jane Smith', 'Finance', 60000.00),
        (3, 'Sam Brown', 'IT', 70000.00),
        (4, 'Nancy White', 'HR', 55000.00),
        (5, 'Mike Green', 'Finance', 65000.00);
        

employee_id	employee_name	department	salary
1	John Doe	HR	50000.00
2	Jane Smith	Finance	60000.00
3	Sam Brown	IT	70000.00
4	Nancy White	HR	55000.00
5	Mike Green	Finance	65000.00

Using SQL DELETE

The SQL DELETE statement is used to remove rows from a table.

Deleting All Rows

To delete all rows from a table, use DELETE FROM table_name:

        DELETE FROM employees;
        

Result:

Rows affected	5

Deleting Rows with a Condition

To delete rows that meet a specific condition, use DELETE FROM table_name WHERE condition:

        DELETE FROM employees
        WHERE department = 'HR';
        

Result:

Rows affected	2

Deleting a Specific Row

To delete a specific row, use DELETE FROM table_name WHERE primary_key_column = value:

        DELETE FROM employees
        WHERE employee_id = 3;
        

Result:

Rows affected	1

Deleting Rows with Multiple Conditions

To delete rows that meet multiple conditions, use DELETE FROM table_name WHERE condition1 AND condition2:

        DELETE FROM employees
        WHERE department = 'Finance' AND salary > 60000.00;
        

Result:

Rows affected	1