with t as (
    select 'B300' as DEPT_CODE,	'2023-04-01' as START_DATE,	null as END_DATE,	 '新設部門' as DEPT_NAME,
    LAYER,	UP_DEPT_CODE,	'1' as VALID_FLG,	UPDATE_USER_NAME,	'2023-04-01 00:00:00' as USER_UPDATE_DATETIME
    from department where dept_code='A100'
)
insert into department select * from t;

update department set END_DATE = '2023-03-31' where dept_code='A100' or dept_code='A200';
update department set valid_flg = '0' where dept_code='A100' or dept_code='A200';
update department set UPDATE_USER_NAME = 'ORG_CHG' where dept_code='A100' or dept_code='A200';
update department set USER_UPDATE_DATETIME = '2023-03-31 23:59:59' where dept_code='A100' or dept_code='A200';

-- select * from emp;
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 (dept_code='A100' or dept_code='A200') and valid_flg='1';
-- update emp set DEPT_CODE ='B300' where (dept_code='A100' or dept_code='A200') and valid_flg='1';
select * from emp