How to Delete Data with SQL?

Welcome back to our SQL tutorial series! So far, we’ve covered how to insert and update data in your database. In this article, we’re going to explore the DELETE statement, which allows you to remove records from a table. Knowing how to use DELETE effectively is crucial for maintaining and cleaning up your database.

Understanding the DELETE Statement

The DELETE statement is used to remove existing records from a table. It is important to use the WHERE clause to specify which records should be deleted; otherwise, all records in the table will be deleted.

Basic Syntax of DELETE

The basic syntax of the DELETE statement is as follows:

DELETE FROM table_name
WHERE condition;

Let’s look at some practical examples to understand how to use the DELETE statement effectively.

Example Table: employees

We’ll continue to use the employees table for our examples:

Deleting a Single Record

To delete a specific employee from the employees table, you use:

DELETE FROM employees
WHERE id = 5;

This command deletes the record with id 5 (Sarah Wilson):

Deleting Multiple Records

To delete all employees who are Data Analysts, you use:

DELETE FROM employees
WHERE position = 'Data Analyst';

This command deletes all records where the position is ‘Data Analyst’:

Using DELETE with Multiple Conditions

You can combine multiple conditions in the WHERE clause using AND or OR. For example, to delete employees who are Software Engineers and have a salary less than $80,000:

DELETE FROM employees
WHERE position = 'Software Engineer' AND salary < 80000.00;

This command deletes all records where the position is ‘Software Engineer’ and the salary is less than $80,000:

Deleting All Records

To delete all records from a table, you use:

DELETE FROM employees;

This command deletes all records in the employees table, leaving it empty:

Note: Be very careful when using the DELETE statement without a WHERE clause, as it will remove all records from the table.

Using DELETE with Subqueries

You can also use subqueries in the WHERE clause to delete records based on data from other tables or the same table. For example, to delete employees who have a salary below the average salary:

DELETE FROM employees
WHERE salary < (SELECT AVG(salary) FROM employees);

This command deletes all records where the salary is below the average salary of the employees.

Conclusion

The DELETE statement is a powerful tool for removing records from your SQL database. Whether you need to delete a single record, multiple records, or all records based on specific conditions, understanding how to use DELETE is essential for effective database management.

In our next tutorial, we’ll explore more advanced SQL topics and commands. Stay tuned and happy coding!