The Project | About Us | Contribute | Donations | License

HOME

The DELETE statement in SQL is used to remove one or more rows from a table. This operation is crucial for maintaining and managing the data within your database. In this section, we will cover the syntax, practical examples, and common use cases for the DELETE statement.

Syntax

The basic syntax for the DELETE statement is as follows:

DELETE FROM table_name
WHERE condition;

table_name: The name of the table from which you want to delete rows.
condition: The condition that specifies which rows should be deleted. If you omit the WHERE clause, all rows in the table will be deleted.

Key Concepts

DELETE with WHERE Clause: Deletes specific rows that meet the condition.
DELETE without WHERE Clause: Deletes all rows in the table.
DELETE with JOIN: Deletes rows based on a condition involving multiple tables.
DELETE with Subquery: Deletes rows based on a condition derived from a subquery.

Practical Examples

Example 1: Deleting Specific Rows

Suppose we have a table named employees:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (id, name, department, salary) VALUES
(1, 'John Doe', 'HR', 50000),
(2, 'Jane Smith', 'Finance', 60000),
(3, 'Sam Brown', 'IT', 70000);

To delete the employee with id 2:

DELETE FROM employees
WHERE id = 2;

Example 2: Deleting All Rows

To delete all rows from the employees table:

DELETE FROM employees;

Example 3: Deleting Rows with a Condition

To delete all employees in the 'HR' department:

DELETE FROM employees
WHERE department = 'HR';

Example 4: Deleting Rows Using a Subquery

Suppose we have another table departments:

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO departments (id, name) VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

To delete all employees who work in departments that no longer exist in the departments table:

DELETE FROM employees
WHERE department NOT IN (SELECT name FROM departments);

Practical Exercises

Exercise 1: Deleting Specific Rows

Given the employees table, delete the employee named 'Sam Brown'.

Solution:

DELETE FROM employees
WHERE name = 'Sam Brown';

Exercise 2: Deleting Rows with a Condition

Delete all employees with a salary less than 60000.

Solution:

DELETE FROM employees
WHERE salary < 60000;

Exercise 3: Deleting Rows Using a Subquery

Given the employees and departments tables, delete all employees who work in the 'Finance' department.

Solution:

DELETE FROM employees
WHERE department = (SELECT name FROM departments WHERE name = 'Finance');

Common Mistakes and Tips

Omitting the WHERE Clause: Be cautious when omitting the WHERE clause, as it will delete all rows in the table.
Referential Integrity: Ensure that deleting rows does not violate referential integrity constraints, especially when foreign keys are involved.
Backup Data: Always backup your data before performing delete operations, especially when dealing with critical data.
Use Transactions: For critical operations, use transactions to ensure that you can roll back changes if something goes wrong.

Conclusion

The DELETE statement is a powerful tool for managing data within your SQL database. By understanding its syntax and various use cases, you can effectively remove unwanted data while maintaining the integrity of your database. In the next section, we will explore the UPSERT (MERGE) statement, which allows you to insert or update data based on specific conditions.

DELETE Statement

Syntax

Key Concepts

Practical Examples

Example 1: Deleting Specific Rows

Example 2: Deleting All Rows

Example 3: Deleting Rows with a Condition

Example 4: Deleting Rows Using a Subquery

Practical Exercises

Exercise 1: Deleting Specific Rows

Exercise 2: Deleting Rows with a Condition

Exercise 3: Deleting Rows Using a Subquery

Common Mistakes and Tips

Conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project