Recipe4.10.Updating with Values from Another Table

Recipe 4.10. Updating with Values from Another Table

Problem

You wish to update rows in one table using values from another. For example, you have a table called NEW_SAL, which holds the new salaries for certain employees. The contents of table NEW_SAL are:

  select *   from new_sal DEPTNO        SAL ------ ----------     10       4000 

Column DEPTNO is the primary key of table NEW_SAL. You want to update the salaries and commission of certain employees in table EMP using values table NEW_SAL if there is a match between EMP.DEPTNO and NEW_SAL.DEPTNO, update EMP.SAL to NEW_SAL.SAL, and update EMP.COMM to 50% of NEW_SAL.SAL. The rows in EMP are as follows:

  select deptno,ename,sal,comm   from emp  order by 1 DEPTNO ENAME             SAL       COMM ------ ---------- ---------- ----------     10 CLARK           2450                 10 KING            5000     10 MILLER          1300     20 SMITH            800     20 ADAMS           1100     20 FORD            3000     20 SCOTT           3000     20 JONES           2975     30 ALLEN           1600         300     30 BLAKE           2850     30 MARTIN          1250        1400     30 JAMES            950     30 TURNER          1500           0     30 WARD            1250         500 

Solution

Use a join between NEW_SAL and EMP to find and return the new COMM values to the UPDATE statement. It is quite common for updates such as this one to be performed via correlated subquery. Another technique involves creating a view (traditional or inline, depending on what your database supports), then updating that view.

DB2 and MySQL

Use a correlated subquery to set new SAL and COMM values in EMP. Also use a correlated subquery to identify which rows from EMP should be updated:

 1 update emp e set (e.sal,e.comm) = (select ns.sal, ns.sal/2 2                                      from new_sal ns 3                                     where ns.deptno=e.deptno) 4  where exists ( select null 5                   from new_sal ns 6                  where ns.deptno = e.deptno ) 

Oracle

The method for the DB2 solution will certainly work for Oracle, but as an alternative, you can update an inline view:

 1 update ( 2  select e.sal as emp_sal, e.comm as emp_comm, 3         ns.sal as ns_sal, ns.sal/2 as ns_comm 4    from emp e, new_sal ns 5   where e.deptno = ns.deptno 6 ) set emp_sal = ns_sal, emp_comm = ns_comm 

PostgreSQL

The method used for the DB2 solution will work for PostgreSQL, but as an alternative you can (quite conveniently) join directly in the UPDATE statement:

 1 update emp 2    set sal = ns.sal, 3        comm = ns.sal/2 4   from new_sal ns 5  where ns.deptno = emp.deptno 

SQL Server

The method used for the DB2 solution will work for SQL Server, but as an alternative you can (similarly to the PostgreSQL solution) join directly in the UPDATE statement:

 1 update e 2    set e.sal  = ns.sal, 3        e.comm = ns.sal/2 4   from emp e, 5        new_sal ns 6  where ns.deptno = e.deptno 

Discussion

Before discussing the different solutions, I'd like to mention something important regarding updates that use queries to supply new values. A WHERE clause in the subquery of a correlated update is not the same as the WHERE clause of the table being updated. If you look at the UPDATE statement in the "Problem" section, the join on DEPTNO between EMP and NEW_SAL is done and returns rows to the SET clause of the UPDATE statement. For employees in DEPTNO 10, valid values are returned because there is a match DEPTNO in table NEW_SAL. But what about employees in the other departments? NEW_SAL does not have any other departments, so the SAL and COMM for employees in DEPTNOs 20 and 30 are set to NULL. Unless you are doing so via LIMIT or TOP or whatever mechanism your vendor supplies for limiting the number of rows returned in a result set, the only way to restrict rows from a table in SQL is to use a WHERE clause. To correctly perform this UPDATE, use a WHERE clause on the table being updated along with a WHERE clause in the correlated subquery.

DB2 and MySQL

To ensure you do not update every row in table EMP, remember to include a correlated subquery in the WHERE clause of the UPDATE. Performing the join (the correlated subquery) in the SET clause is not enough. By using a WHERE clause in the UPDATE, you ensure that only rows in EMP that match on DEPTNO to table NEW_SAL are updated. This holds true for all RDBMSs.

Oracle

In the Oracle solution using the update join view, you are using equi-joins to determine which rows will be updated. You can confirm which rows are being updated by executing the query independently. To be able to successfully use this type of UPDATE, you must first understand the concept of key-preservation. The DEPTNO column of the table NEW_SAL is the primary key of that table, thus its values are unique within the table. When joining between EMP and NEW_SAL, however, NEW_SAL.DEPTNO is not unique in the result set, as can be seen below:

  select e.empno, e.deptno e_dept, ns.sal, ns.deptno ns_deptno   from emp e, new_sal ns  where e.deptno = ns.deptno EMPNO     E_DEPT        SAL  NS_DEPTNO ----- ---------- ---------- ----------  7782         10       4000         10  7839         10       4000         10  7934         10       4000         10 

To enable Oracle to update this join, one of the tables must be key-preserved, meaning that if its values are not unique in the result set, it should at least be unique in the table it comes from. In this case NEW_SAL has a primary key on DEPTNO, which makes it unique in the table. Because it is unique in its table, it may appear multiple times in the result set and will still be considered key-preserved, thus allowing the update to complete successfully.

PostgreSQL and SQL Server

The syntax is a bit different between these two platforms, but the technique is the same. Being able to join directly in the UPDATE statement is extremely convenient. Since you specify which table to update (the table listed after the UPDATE keyword) there's no confusion as to which table's rows are modified. Additionally, because you are using joins in the update (since there is an explicit WHERE clause), you can avoid some of the pitfalls when coding correlated subquery updates; in particular, if you missed a join here, it would be very obvious you'd have a problem.