14일차 # 6-17: Update Case (Correlated UPDATE)

        drop table d1 purge;

drop table e1 purge;

create table d1 as select * from dept;

create table e1 as select * from emp;

  (1) 집계하는 경우 

alter table d1 add (sum_sal number);

update d1 

set sum_sal = (select sum(sal) from e1

      where deptno = 10)

where deptno = 10;

select * from d1;

↓↓

update d1 

set sum_sal = (select sum(sal) from e1

              where e1.deptno = d1.deptno);

 

select * from d1;

  (2) 컬럼을 추가하는 경우 

alter table e1 add (dname varchar2(30));

update e1 

set dname = 'ACCOUNTING'

where deptno = 10;

select * from e1;

↓↓

update e1

set dname = (select dname from d1

            where deptno = e1.deptno);

select * from e1;