How to Delete Records using a Subquery in SQL?

Delete using Subquery

Write a SQL query to delete records from a table using a subquery to determine the condition.

Solution:

-- Delete employees who work in the same department as EmployeeID = 2.
DELETE FROM Employees -- Specify the table to delete from.
WHERE DepartmentID = (SELECT DepartmentID FROM Employees WHERE EmployeeID = 2);
-- Use a subquery to find the department of EmployeeID = 2.

Explanation:

1. Purpose of the Query :

1. The goal is to delete all employees who belong to the same department as the employee with EmployeeID = 2.
2. This demonstrates how to use a subquery within a DELETE statement to dynamically determine which rows to delete.

2. Key Components :

1. DELETE FROM Employees : Specifies the table from which records will be deleted.
2. WHERE DepartmentID = (SELECT DepartmentID FROM Employees WHERE EmployeeID = 2) :
1. The subquery retrieves the DepartmentID of the employee with EmployeeID = 2.
2. The WHERE clause ensures that only employees in the same department are deleted.

3. Why use a Subquery? :

1. A subquery allows you to dynamically retrieve values from the database and use them as part of the deletion condition.
2. For example, if you want to remove employees based on relationships or attributes stored in other rows, a subquery provides flexibility.

4. Real-World Application :

1. For example, in a company database, you might use this query to delete all employees in the same department as a specific employee (e.g., EmployeeID = 2) as part of a restructuring process or department closure.

Additional Notes:

• Subqueries are powerful tools for working with related data across rows or tables.
• Scenarios where using a subquery is appropriate, such as:
• Deleting records based on relationships (e.g., employees in the same department).
• Dynamically retrieving values that cannot be hardcoded into the query.

For more Practice: Solve these Related Problems:

• Write a SQL query to delete records from the `employees` table where the `department_id` matches a subquery that identifies closed departments.
• Write a SQL query to delete records from the `products` table where the `category_id` matches a subquery that identifies discontinued categories.
• Write a SQL query to delete records from the `orders` table where the `customer_id` matches a subquery that identifies inactive customers.
• Write a SQL query to delete records from the `inventory` table where the `item_id` matches a subquery that identifies obsolete items.

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

Previous SQL Exercise: Delete using Subquery.
Next SQL Exercise: Insert from another Table.