SQL - Delete

The DELETE statement in SQL is used to remove existing records from a table. Here’s a detailed tutorial on how to use the DELETE statement effectively.

Basic Syntax

DELETE FROM table_name
WHERE condition;

Steps to Delete Data

1. Specify the table name: Indicate the table from which you want to delete data.
2. Provide the condition: Use the WHERE clause to specify which rows should be deleted. If you omit the WHERE clause, all rows in the table will be deleted.

Examples

1. Deleting Specific Rows

Assume you have a table named employees with columns first_name, last_name, age, and department.

Delete a specific employee named John Doe:

DELETE FROM employees
WHERE first_name = 'John' AND last_name = 'Doe';

2. Deleting Rows Based on a Condition

Delete all employees who are older than 60 years:

DELETE FROM employees
WHERE age > 60;

3. Deleting All Rows

To delete all rows from a table (note that this does not delete the table itself):

DELETE FROM employees;

or alternatively:

TRUNCATE TABLE employees;

Note: TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE, but it cannot be rolled back in most systems and does not fire triggers.

Using Subqueries in Delete

You can use subqueries in the WHERE clause to delete rows based on conditions from another table:

Assume there is another table named departments with a column department_name. Delete employees who belong to departments that are to be closed:

DELETE FROM employees
WHERE department IN (SELECT department_name FROM departments WHERE status = 'closed');