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.