Recipe4.9.Updating when Corresponding Rows Exist

Recipe 4.9. Updating when Corresponding Rows Exist

Problem

You want to update rows in one table when corresponding rows exist in another. For example, if an employee appears in table EMP_BONUS, you want to increase that employee's salary (in table EMP) by 20 percent. The following result set represents the data currently in table EMP_BONUS:

  select empno, ename   from emp_bonus      EMPNO ENAME ---------- ---------       7369 SMITH       7900 JAMES       7934 MILLER 

Solution

Use a subquery in your UPDATE statement's WHERE clause to find employees in table EMP that are also in table EMP_BONUS. Your UPDATE will then act only on those rows, enabling you to increase their salary by 20 percent:

 1 update emp 2    set sal=sal*1.20 3  where empno in ( select empno from emp_bonus ) 

Discussion

The results from the subquery represent the rows that will be updated in table EMP. The IN predicate tests values of EMPNO from the EMP table to see whether they are in the list of EMPNO values returned by the subquery. When they are, the corresponding SAL values are updated.

Alternatively, you can use EXISTS instead of IN:

 update emp    set sal = sal*1.20  where exists ( select null                   from emp_bonus                  where emp.empno=emp_bonus.empno ) 

You may be surprised to see NULL in the SELECT list of the EXISTS subquery. Fear not, that NULL does not have an adverse effect on the update. In my opinion it increases readability as it reinforces the fact that, unlike the solution using a subquery with an IN operator, what will drive the update (i.e., which rows will be updated) will be controlled by the WHERE clause of the subquery, not the values returned as a result of the subquery's SELECT list.