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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23895263/viewspace-681076/