insert into DEPARTMENT
 (DEPT_CODE, START_DATE, END_DATE, DEPT_NAME, LAYER, UP_DEPT_CODE, VALID_FLG, UPDATE_USER_NAME, USER_UPDATE_DATETIME)
select
  'B300'
 ,'2023-04-01'
 ,null
 ,'新設部門'
 ,LAYER
 ,UP_DEPT_CODE
 ,'1'
 ,UPDATE_USER_NAME
 ,'2023-04-01 00:00:00'
from
 DEPARTMENT
where
 DEPT_CODE = 'A100'
;
update DEPARTMENT set END_DATE = '2023-03-31', VALID_FLG = '0', UPDATE_USER_NAME = 'ORG_CHG', USER_UPDATE_DATETIME = '2023-03-31 23:59:59' where DEPT_CODE in ('A100', 'A200');
update
 DEPARTMENT
set
 DEPT_CODE ='B300', START_DATE = '2023-04-01', UPDATE_USER_NAME = 'ORG_CHG', USER_UPDATE_DATETIME = '2023-04-01 00:00:00'
where
 DEPT_CODE
  in
 (
  select
   e.DEPT_CODE
  from
   DEPARTMENT d
    join
   EMP e
    on
   d.DEPT_CODE = e.DEPT_CODE
  where
   e.DEPT_CODE in ('A100', 'A200')
    and
   e.VALID_FLG = '1'
 )
;