-- delete
select *
from ITEM
where
    ITEM_CODE in (
        (
        select i.ITEM_CODE
        from ITEM i left join STOCK s on i.ITEM_CODE = s.ITEM_CODE
        group by i.ITEM_CODE
        having
            count(s.ITEM_CODE) = 0
        or
            (
                sum(s.ACTUAL_AMT) = 0
            and
                max(ifnull(s.LAST_DELIVERY_DATE, '2999-12-31')) <= date('2023-06-01', '-6 months')
            )
        )
    )
and
    STOCK_MANAGEMENT_TYPE = 1
;