delete from ITEM
where ITEM_CODE in
    (
        select
            ITEM_CODE
        from ITEM
        where STOCK_MANAGEMENT_TYPE=1
        
        except
        
        select
            ITEM_CODE
        from STOCK
        group by ITEM_CODE
        
        union
        
        select
            ITEM_CODE
        from
            (select
                STOCK.ITEM_CODE
                , row_number()
                    over(partition by STOCK.ITEM_CODE order by STOCK.LAST_DELIVERY_DATE desc) as ROW_NUM
                , STOCK.LAST_DELIVERY_DATE
                , STOCK.ACTUAL_AMT
            from STOCK
            left join ITEM on STOCK.ITEM_CODE=ITEM.ITEM_CODE
            where ITEM.STOCK_MANAGEMENT_TYPE=1)
        where ROW_NUM=1
            and LAST_DELIVERY_DATE<date('2023-06-01', '-6 month') and ACTUAL_AMT=0
    ) 
;