rem drop the table if it exists 

begin 
  execute immediate 'drop table emp purge'; 
exception 
  when others then 
    null; -- if it fails, do nothing 
end; 

rem create the table 

create table emp 
       (empno number(4) not null, 
        ename varchar2(10), 
        job varchar2(9), 
        mgr number(4), 
        hiredate date, 
        sal number(7, 2), 
        comm number(7, 2), 
        deptno number(2)) 

rem add the data 

begin 
  insert into emp 
   values (7369,'SMITH','CLERK',7902,to_date('17-DEC-1980', 'DD-MON-YYYY'),800,null,20); 
  insert into emp 
   values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-1981', 'DD-MON-YYYY'),1600,300,30); 
  insert into emp 
   values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-1981', 'DD-MON-YYYY'),1250,500,30); 
  insert into emp 
   values (7566,'JONES','MANAGER',7839,to_date('2-APR-1981', 'DD-MON-YYYY'),2975,null,20); 
  insert into emp 
   values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-1981', 'DD-MON-YYYY'),1250,1400,30); 
  insert into emp 
   values (7698,'BLAKE','MANAGER',7839,to_date('1-MAY-1981', 'DD-MON-YYYY'),2850,null,30); 
  insert into emp 
   values (7782,'CLARK','MANAGER',7839,to_date('9-JUN-1981', 'DD-MON-YYYY'),2450,null,10); 
  insert into emp 
   values (7788,'SCOTT','ANALYST',7566,to_date('09-DEC-1982', 'DD-MON-YYYY'),3000,null,20); 
  insert into emp 
   values (7839,'KING','PRESIDENT',null,to_date('17-NOV-1981', 'DD-MON-YYYY'),5000,null,10); 
  insert into emp 
   values (7844,'TURNER','SALESMAN',7698,to_date('8-SEP-1981', 'DD-MON-YYYY'),1500,0,30); 
  insert into emp 
   values (7876,'ADAMS','CLERK',7788,to_date('12-JAN-1983', 'DD-MON-YYYY'),1100,null,20); 
  insert into emp 
   values (7900,'JAMES','CLERK',7698,to_date('3-DEC-1981', 'DD-MON-YYYY'),950,null,30); 
  insert into emp 
   values (7902,'FORD','ANALYST',7566,to_date('3-DEC-1981', 'DD-MON-YYYY'),3000,null,20); 
  insert into emp 
   values (7934,'MILLER','CLERK',7782,to_date('23-JAN-1982', 'DD-MON-YYYY'),1300,null,10); 
end; 

rem perform a commit to make the data persistent 

commit 

rem add two columns to the table (this does an implicit commit so the previous one was not necessary 

alter table emp add (mgr_name varchar2(10) 
                    ,mgr_job varchar2(9) 
                    ) 
                     
rem describe the current layout 
desc emp 

rem read the data 

select * 
  from emp e 

rem update the mgr_name column 

update emp e 
   set e.mgr_name = (select m.ename 
                       from emp m 
                      where 1=1 
                        and m.empno = e.mgr) 
 where 1=1 
   and e.mgr is not null 

rem update the mgr_job column 

update emp e 
   set e.mgr_job = (select m.job 
                       from emp m 
                      where 1=1 
                        and m.empno = e.mgr) 
 where 1=1 
   and e.mgr is not null 

rem read the data 

select * 
  from emp e 

rem rollback the updates 

rollback 

rem read the data 

select * 
  from emp e 

rem update the mgr_name and mgr_job columns in one statement but with two subqueries 

update emp e 
   set e.mgr_name = (select m.ename 
                       from emp m 
                      where 1=1 
                        and m.empno = e.mgr) 
     , e.mgr_job = (select m.job 
                       from emp m 
                      where 1=1 
                        and m.empno = e.mgr) 
 where 1=1 
   and e.mgr is not null 

rem read the data 

select * 
  from emp e 

rem rollback the updates 

rollback 

rem read the data 

select * 
  from emp e 

rem update the mgr_name and mgr_job columns in one statement with one subquery 

update emp e 
   set (e.mgr_name, e.mgr_job) = (select m.ename, m.job 
                                   from emp m 
                                  where 1=1 
                                    and m.empno = e.mgr) 
 where 1=1 
   and e.mgr is not null 

rem read the data 

select * 
  from emp e