with
    --削除しないデータを抽出
    sub as (
        select
            ITEM.ITEM_CODE as ITEM_CODE
        from
            ITEM
            inner join STOCK
                on ITEM.ITEM_CODE = STOCK.ITEM_CODE
        where
            COALESCE(STOCK.LAST_DELIVERY_DATE,'9999-12-31') > DATE('2023-06-01', '-6 months')
            or STOCK.ACTUAL_AMT <> 0
        )
    
    --削除用（念のため、delete文の前に準備）
    ,sub2 as (
        select
            ITEM.ITEM_CODE as ITEM_CODE
        from 
            ITEM
        where
            NOT EXISTS(
                select * from sub
                where ITEM.ITEM_CODE = sub.ITEM_CODE)
            and ITEM.STOCK_MANAGEMENT_TYPE = 1
        )
--select * from sub2

delete
from ITEM 
where ITEM_CODE in (
    select ITEM_CODE
    from sub2
    );
--select * from ITEM