多表关联更新

一个面试题，多表关联更新一张表中的一个字段，下面是题目：
按以下条件，写一个或两个SQL，更新Employee表的Salary字段

Employee
employeeid titleid salary
A1001 Manager 3000
A1002 Developer 2000
A1003 SA 2000
A1004 Operator 2000
A1005 Developer 2000
A1006 Developer 2000
A1007 Developer 2000
.. .. ..
.. .. ..

Add_salary
Titleid addsalary
Manager 10%
Developer 20%
SA 30%
.. ..
.. ..

Add_bonus
employeeid addbonus
A1001 200
A1002 300
A1003 300
A1004 100
.. ..
.. ..


- 所有员工的salary会按照（Add_salary）表的升幅率进行相应的调整
- 只有部分员工按(Add_bonus)表获得相应的奖金

初始化测试数据，以及错误和正确的方法：
[code]SQL> select * from emp;

EMPNO DEPTID SAL
----- ---------- ----------
A1001 Manager 3000
A1002 Developer 2000
A1003 SA 2000
A1004 Operator 2000
A1005 Developer 2000
A1006 Developer 2000
A1007 Developer 2000

SQL> select * from add_sal;

DEPTID ADDSAL
---------- ----------
Manager 1.1
Developer 1.2
SA 1.3


SQL> select * from add_bonus;

EMPNO ADDBONUS
----- ----------
A1001 200
A1002 300
A1003 300
A1004 100[/code]

一条语句更新emp表的sal字段

-----错误方法：
[code]SQL> update emp a set sal=(select NVL( a.sal,1)*(nvl(b.addsal,1))+nvl(c.addbonus,0) from add_sal b,add_bonus c where a.empno=c.empno(+) and a.deptid=b.deptid(+) );

7 rows updated.

Elapsed: 00:00:00.00
SQL> SELECT * FROM EMP;

EMPNO DEPTID SAL
----- ---------- ----------
A1001 Manager 3500
A1002 Developer 2700
A1003 SA 2900
A1004 Operator
A1005 Developer
A1006 Developer
A1007 Developer

7 rows selected.

Elapsed: 00:00:00.00
SQL> ROLLBACK;

Rollback complete.[/code]
正确方法：
[code]SQL> update emp a set sal=(select afteradd from (select a.empno,NVL(a.sal,1)*(nvl(b.addsal,1))+nvl(c.addbonus,0) afteradd from emp a, add_sal b,add_bonus c where a.empno=c.empno(+) and a.deptid=b.deptid(+)) d where a.empno=d.empno);

7 rows updated.

Elapsed: 00:00:00.02
SQL> select * from emp;

EMPNO DEPTID SAL
----- ---------- ----------
A1001 Manager 3500
A1002 Developer 2700
A1003 SA 2900
A1004 Operator 2100
A1005 Developer 2400
A1006 Developer 2400
A1007 Developer 2400

7 rows selected.

Elapsed: 00:00:00.00
SQL>[/code]