Recipe 4.10. Updating with Values from Another Table

Problem

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

	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 )

------------------

	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

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23895263/viewspace-681076/，如需轉載，請註明出處，否則將追究法律責任。