How to Update Records using a Subquery in SQL

Update using Subquery

Write a SQL query to update records in a table using a subquery to determine the condition.

Solution:

-- Update the salary of employees in the same department as EmployeeID = 1.
UPDATE Employees -- Specify the table to update.
SET Salary = Salary + 5000 -- Increase the salary by 5000.
WHERE DepartmentID = (SELECT DepartmentID FROM Employees WHERE EmployeeID = 1);
-- Use a subquery to find the department of EmployeeID = 1.

Explanation:

1. Purpose of the Query :

1. The goal is to increase the salary of all employees who belong to the same department as the employee with EmployeeID = 1.
2. This demonstrates how to use a subquery within an UPDATE statement to dynamically determine which rows to update.

2. Key Components :

1. UPDATE Employees : Specifies the table where the update will occur.
2. SET Salary = Salary + 5000 : Increases the salary of the targeted employees by 5,000.
3. WHERE DepartmentID = (SELECT DepartmentID FROM Employees WHERE EmployeeID = 1) :
1. The subquery retrieves the DepartmentID of the employee with EmployeeID = 1.
2. The WHERE clause ensures that only employees in the same department are updated.

3. Why use a Subquery? :

1. A subquery allows you to dynamically retrieve values from the database and use them as part of the update condition.
2. For example, if you want to apply changes to 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 give a salary raise to all employees in the same department as a specific employee (e.g., EmployeeID = 1) as part of a department-wide incentive program.

Additional Notes:

• Subqueries are powerful tools for working with related data across rows or tables.
• Scenarios where using a subquery is appropriate, such as:
• Updating records based on relationships (e.g., employees in the same department).
• Dynamically retrieving values that cannot be hardcoded into the query.
• Important Considerations :
• Ensure that the subquery returns a single value when used in a scalar context (e.g., WHERE DepartmentID = ...).
• Test the subquery independently to verify that it retrieves the expected results before integrating it into the UPDATE statement.

For more Practice: Solve these Related Problems:

• Write a SQL query to update the `manager_id` in the `employees` table based on a subquery that retrieves the `id` of the highest-paid employee.
• Write a SQL query to update the `price` in the `products` table based on a subquery that calculates the average price of products in the same category.
• Write a SQL query to update the `status` in the `orders` table based on a subquery that identifies orders placed by inactive customers.
• Write a SQL query to update the `quantity` in the `inventory` table based on a subquery that calculates the total quantity sold for each item.

For more Practice: Solve these Related Problems:

• Write a SQL query to update the `manager_id` in the `employees` table based on a subquery that retrieves the `id` of the highest-paid employee.
• Write a SQL query to update the `price` in the `products` table based on a subquery that calculates the average price of products in the same category.
• Write a SQL query to update the `status` in the `orders` table based on a subquery that identifies orders placed by inactive customers.
• Write a SQL query to update the `quantity` in the `inventory` table based on a subquery that calculates the total quantity sold for each item.

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

Previous SQL Exercise: Update Multiple Records
Next SQL Exercise: Delete Using Subquery.