先查询:
select * from emp e left join dept t on e.deptno=t.deptno) set sal =( select (case when e.job='MANAGER' and t.loc='DALLAS' then sal *1.15
when e.job='CLERK' and t.loc='NEW YORK' then sal*0.95 else sal end) t
from emp e join dept t on e.deptno=t.deptno)
select e.job,t.dname,e.sal, case when t.dname='SALES' and e.sal<1500 then sal *1.15
when t.dname='RESEARCH' and e.job='CLERK' then sal*1.05 else sal end
from emp e join dept t on e.deptno=t.deptno
再更新:update (select * from emp e left join dept t on e.deptno = t.deptno) T
set T.sal = (case
when t.job = 'MANAGER' and t.loc = 'DALLAS' then
T.sal * 1.15
when t.job = 'CLERK' and t.loc = 'NEW YORK' then
T.sal * 0.95
when T.dname = 'SALES' and T.sal < 1500 then
T.sal * 1.15
when T.dname = 'RESEARCH' and T.job = 'CLERK' then
T.sal * 1.05
else
T.sal
end);
更新方法二:
merge into emp m
using (select empno,
(case
when e.job = 'MANAGER' and t.loc = 'DALLAS' then
sal * 1.15
when e.job = 'CLERK' and t.loc = 'NEW YORK' then
sal * 0.95
when t.dname = 'SALES' and e.sal < 1500 then
sal * 1.15
when t.dname = 'RESEARCH' and e.job = 'CLERK' then
sal * 1.05
else
sal
end) t
from emp e
join dept t
on e.deptno = t.deptno) n
on (m.empno = n.empno)
when matched then
update set m.sal = n.t;