with tmp as (
--最大出荷日が6か月以上前で在庫ゼロ
select item_code as code 
from stock 
group by item_code 
having max(last_delivery_date)<=date('2023-06-01','-6 months') and sum(actual_amt)=0
) 
,tmp1 as (
--stockにないitem
select item_code as CODE 
from item 
where item.item_code not in (select item_code from stock) 
)
delete from item 
where item.STOCK_MANAGEMENT_TYPE=1 
and (item_code in (select code from tmp) 
or item_code in (select code from tmp1));