How to Delete Multiple Records from a SQL Table Based on Condition

Delete Multiple Records

Write a SQL query to delete multiple records from a table based on a specific condition.

Solution:

-- Delete all employees with a salary less than 50000.
DELETE FROM Employees -- Specify the table to delete from.
WHERE Salary < 50000; -- Delete employees earning less than 50000.

Explanation:

• Purpose of the Query :
• The goal is to delete all employees in the Employees table whose salary is less than 50,000.
• This demonstrates how to use the DELETE statement to remove multiple rows from a table based on a condition.
• Key Components :
• DELETE FROM Employees : Specifies the table from which the records will be deleted.
• WHERE Salary < 50000 : Filters the rows to apply the deletion only to employees whose salary is below 50,000.
• Why use DELETE with a Condition? :
• Deleting multiple records based on a condition is useful when you need to remove a subset of rows that meet specific criteria.
• For example, if a company decides to remove records of employees earning below a certain threshold, this query ensures that only those employees are affected.
• Real-World Application :
• For example, in a company database, you might use this query to clean up records of employees who earn less than 50,000 as part of a cost-cutting measure or data cleanup process.

Additional Notes:

• Deleting multiple records in one query is more efficient than deleting them individually.
• Scenarios where deleting multiple records is appropriate, such as:
• Cleaning up outdated or redundant data (e.g., employees with outdated salaries).
• Removing records that no longer meet business requirements.

Important Considerations :

• Ensure that the WHERE clause accurately filters the intended rows to avoid unintended deletions.
• Test the query on a small dataset or in a development environment before applying it to production data.

For more Practice: Solve these Related Problems:

• Write a SQL query to delete all records from the 'students' table where the 'grade' is 'F'.
• Write a SQL query to delete all records from the 'inventory' table where the 'quantity' is less than 10.
• Write a SQL query to delete all records from the 'orders' table where the 'order_date' is older than two years.
• Write a SQL query to delete all records from the 'products' table where the 'category' is 'Discontinued'.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Delete Single Record
Next SQL Exercise: Insert with Default Values.