insert into DEPARTMENT values
(
  'B300',
  '2023-04-01',
  NULL,
  '新設部門',
  (select layer from DEPARTMENT where dept_code = 'A100' limit 1),
  (select up_dept_code from DEPARTMENT where dept_code = 'A100' limit 1),
  '1',
  (select UPDATE_USER_NAME from DEPARTMENT where dept_code = 'A100' limit 1),
  '2023-04-01 00:00:00'
);
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');

with emps as (
select emp_code
from emp e 
left join department d using(dept_code)
where d.dept_code in ('A100', 'A200') and e.valid_flg = '1'
)
update emp
set DEPT_CODE ='B300',
START_DATE = '2023-04-01',
UPDATE_USER_NAME = 'ORG_CHG',
USER_UPDATE_DATETIME = '2023-04-01 00:00:00'
where emp_code in (select emp_code from emps);

select * from emp