Writing UPDATE and DELETE Statements Containing Subqueries

So far, you ve only seen subqueries contained in a SELECT statement. As you ll see in this section, you can also use subqueries with UPDATE and DELETE statements.

Writing an UPDATE Statement Containing a Subquery

In an UPDATE statement, you set the new column value equal to the result returned by a single row subquery. For example, the following UPDATE statement sets employee #4 s salary to the average of the high salary grades returned by a subquery:

  UPDATE employees   SET salary =   (SELECT AVG(high_salary)   FROM salary_grades)   WHERE employee_id = 4;  1 row updated. 

This increases employee #4 s salary from $500,000 to $625,000 (this is the average of the high salaries from the salary_grades table).

Note  

If you execute the UPDATE statement, remember to execute a ROLLBACK to undo the change.

Writing a DELETE Statement Containing a Subquery

You use the results returned by the subquery in the WHERE clause of your DELETE statement. For example, the following DELETE statement removes the employee whose salary is greater than the average of the high salary grades returned by a subquery:

  DELETE FROM employees   WHERE salary  >  (SELECT AVG(high_salary)   FROM salary_grades);  1 row deleted. 

This DELETE statement removes employee #1.

Note  

If you execute the DELETE statement, remember to execute a ROLLBACK to undo the removal of the row.