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.
** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table
0 Comments:
Post a Comment