update关联更新

create table emp(EMPNO varchar2(20),ENAME varchar2(20),deptno varchar2(20),dname varchar2(20));

insert into emp values(7599,'WORD',30,'CCC');

select * from emp;

    EMPNO ENAME DEPTNO DNAME
1 7369 SMITH 20 AAA
2 7499 ALLEN 10 BBB
3 7599 WORD 30 CCC


create table dept (deptno varchar2(20),dname varchar2(20));

insert into dept values(10,'SALES');
insert into dept values(20,'IT');

insert into dept values(30,'HR');


UPDATE emp
   SET emp.dname =
       (SELECT dept.dname
          FROM dept
         WHERE dept.deptno = emp.deptno
           AND dept.deptno IN (10, 20));
           
select * from emp;
  

    EMPNO ENAME DEPTNO DNAME
1 7369 SMITH 20 IT
2 7499 ALLEN 10 SALES
3 7599 WORD 30

可以看到deptno=30的被更新成空的了

select * fr