Update and Delete Rows Using Correlated Subqueries with SQL

Update and Delete Rows Using Correlated Subqueries

Updating Rows Using Correlated Subqueries

Definition

When updating rows with a correlated subquery, the subquery references columns from the outer query. This allows you to conditionally update rows based on values in related rows.

Example 1: Update Salaries to Exceed the Average Salary of Their Department

Problem: Increase the salary of employees to be 10% more than the average salary of their department.

Solution: 

UPDATE Employees e
SET Salary = Salary * 1.10
WHERE Salary < (
    SELECT AVG(Salary)
    FROM Employees e2
    WHERE e.DepartmentID = e2.DepartmentID
);

Explanation:

• • The subquery (SELECT AVG(Salary) FROM Employees e2 WHERE e.DepartmentID = e2.DepartmentID) calculates the average salary for each department.
  • The outer UPDATE query increases the salary of employees whose current salary is less than this average salary by 10%.

Example 2: Increase Salaries for Employees with Performance Ratings Above the Average

Problem: Increase the salaries of employees whose performance rating is above the average performance rating for their department.

Solution: 

UPDATE Employees e
SET Salary = Salary + 1000
WHERE PerformanceRating > (
    SELECT AVG(PerformanceRating)
    FROM Employees e2
    WHERE e.DepartmentID = e2.DepartmentID
);

Explanation:

• • The subquery calculates the average performance rating within each department.
  • The outer UPDATE query raises the salary by $1000 for employees whose performance rating exceeds the average for their department.

Example 3: Set Bonus for Employees Whose Salary is Below the Department Average

Problem: Set a bonus amount for employees whose salary is below the average salary of their department.

Solution: 

UPDATE Employees e
SET Bonus = 500
WHERE Salary < (
    SELECT AVG(Salary)
    FROM Employees e2
    WHERE e.DepartmentID = e2.DepartmentID
);

Explanation:

• • The subquery finds the average salary in each department.
  • The outer UPDATE query sets a bonus of $500 for employees whose salary is below the average for their department.

Deleting Rows Using Correlated Subqueries

Definition

When deleting rows using a correlated subquery, the subquery determines which rows to delete based on values from related rows in the subquery.

Example 1: Delete Employees Earning Less Than the Average Salary in Their Department

Problem: Remove employees who earn less than the average salary of their department.

Solution: 

DELETE FROM Employees e
WHERE Salary < (
    SELECT AVG(Salary)
    FROM Employees e2
    WHERE e.DepartmentID = e2.DepartmentID
);

Explanation:

• • The subquery calculates the average salary for each department.
  • The outer DELETE query removes employees whose salary is below this average.

Example 2: Remove Employees with Performance Ratings Below the Average for Their Department

Problem: Delete employees whose performance ratings are below the average performance rating for their department.

Solution: 

DELETE FROM Employees e
WHERE PerformanceRating < (
    SELECT AVG(PerformanceRating)
    FROM Employees e2
    WHERE e.DepartmentID = e2.DepartmentID
);

Explanation:

• • The subquery calculates the average performance rating in each department.
  • The outer DELETE query removes employees with performance ratings below the department’s average.

Example 3: Remove Employees Whose Managers are No Longer in the System

Problem: Delete employees whose manager ID refers to a non-existent manager.

Solution: 

DELETE FROM Employees e
WHERE ManagerID NOT IN (
    SELECT EmployeeID
    FROM Employees
);

Explanation:

• • The subquery selects valid employee IDs (i.e., IDs of managers who are still in the system).
  • The outer DELETE query removes employees whose manager ID is not in the list of current employee IDs.

Performance Considerations

• Indexing: Ensure that columns used in correlated subqueries are indexed to improve performance, especially for large tables.
• Subquery Complexity: Correlated subqueries are executed for each row in the outer query, which can be slow if the subquery is complex or if the table is large. Optimize the subquery to minimize execution time.
• Batch Updates/Deletes: For large datasets, consider breaking down the update or delete operation into smaller batches to reduce lock contention and improve performance.

Summary

Correlated subqueries are a powerful tool for conditional updates and deletions in SQL:

• Updating Rows: Use correlated subqueries to conditionally update rows based on related data.
• Deleting Rows: Use correlated subqueries to conditionally delete rows based on related data.