为财务部门的雇员加薪,5年以下加5%,5年以上加3%
UPDATE employee e,
(
#筛选出财务部的员工信息,并作为临时表
SELECT
e.empid,
e.sal,
w.worktime
FROM
employee e,
`work` w
WHERE
e.empid = w.empid
AND w.deptno = (
#选出财务部的编号
SELECT
d.deptno
FROM
department d
WHERE
d.dname = '财务'
)
) tp#临时表别名 tp
#使用CASE WHEN对信息批量修改
SET e.sal = CASE
WHEN tp.worktime >= 5 THEN
e.sal * (1 + 0.05)
WHEN tp.worktime < 5 THEN
e.sal * (1 + 0.03)
END
#修改时满足的条件
WHERE
e.empid = tp.empid;