多表关联update

给EMP增加字段如下：

ALTER TABLE EMP ADD dname VARCHAR2(50) DEFAULT 'UNKNOWN';

SQL> select empno,ename,deptno,dname from emp;
EMPNO ENAME      DEPTNO DNAME
----- ---------- ------ --------------------------------------------------
 7369 SMITH          20 UNKNOWN
 7499 ALLEN          30 UNKNOWN
 7521 WARD           30 UNKNOWN
 7566 JONES          20 UNKNOWN
 7654 MARTIN         30 UNKNOWN
 7698 BLAKE          30 UNKNOWN
 7782 CLARK          10 UNKNOWN
 7788 SCOTT          20 UNKNOWN
 7839 KING           10 UNKNOWN
 7844 TURNER         30 UNKNOWN
 7876 ADAMS          20 UNKNOWN
 7900 JAMES          30 UNKNOWN
 7902 FORD           20 UNKNOWN
 7934 MILLER         10 UNKNOWN
14 rows selected

现在要求把dept.dname IN ('SALES', 'RESEARCH')的dname更新至表emp中
初学oracle的经常用以下语句更新

UPDATE emp
   SET emp.dname =
       (SELECT dept.dname
          FROM dept
         WHERE dept.dname IN ('SALES', 'RESEARCH')
           AND dept.deptno = emp.deptno);

ok,我们来看会发生什么情况

SQL> UPDATE emp
  2     SET emp.dname =
  3         (SELECT dept.dname
  4            FROM dept
  5           WHERE dept.dname IN ('SALES', 'RESEARCH')
  6             AND dept.deptno = emp.deptno);
14 rows updated


SQL> select empno,ename,deptno,dname from emp;
EMPNO ENAME      DEPTNO DNAME
----- ---------- ------ --------------------------------------------------
 7369 SMITH          20 RESEARCH
 7499 ALLEN          30 SALES
 7521 WARD           30 SALES
 7566 JONES          20 RESEARCH
 7654 MARTIN         30 SALES
 7698 BLAKE          30 SALES
 7782 CLARK          10 
 7788 SCOTT          20 RESEARCH
 7839 KING           10 
 7844 TURNER         30 SALES
 7876 ADAMS          20 RESEARCH
 7900 JAMES          30 SALES
 7902 FORD           20 RESEARCH
 7934 MILLER         10 
14 rows selected

注意以上标识的各行。DNAME被更新成了空值，这显然不是我们需要的。想想在生产中大量的这种数据被清空，后果之严重。

原因就在于以上语句未加限定条件，正确语句应如下所示:

SQL> rollback;
Rollback complete


SQL> 
SQL> UPDATE emp
  2     SET emp.dname =
  3         (SELECT dept.dname
  4            FROM dept
  5           WHERE dept.dname IN ('SALES', 'RESEARCH')
  6             AND dept.deptno = emp.deptno)
  7   WHERE EXISTS (SELECT dept.dname
  8            FROM dept
  9           WHERE dept.dname IN ('SALES', 'RESEARCH')
 10             AND dept.deptno = emp.deptno);
11 rows updated


SQL> select empno,ename,deptno,dname from emp;
EMPNO ENAME      DEPTNO DNAME
----- ---------- ------ --------------------------------------------------
 7369 SMITH          20 RESEARCH
 7499 ALLEN          30 SALES
 7521 WARD           30 SALES
 7566 JONES          20 RESEARCH
 7654 MARTIN         30 SALES
 7698 BLAKE          30 SALES
 7782 CLARK          10 UNKNOWN
 7788 SCOTT          20 RESEARCH
 7839 KING           10 UNKNOWN
 7844 TURNER         30 SALES
 7876 ADAMS          20 RESEARCH
 7900 JAMES          30 SALES
 7902 FORD           20 RESEARCH
 7934 MILLER         10 UNKNOWN
14 rows selected
SQL> 

这样，我们就只更新了对应的行，其它行的数据不受影响。