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