WITH DELETE_ITEM AS (
    SELECT
        it.ITEM_CODE
    FROM
        ITEM it
    LEFT OUTER JOIN STOCK st
        ON it.ITEM_CODE = st.ITEM_CODE
    WHERE
        st.ITEM_CODE IS NULL
        AND it.STOCK_MANAGEMENT_TYPE = 1
    UNION
    SELECT
        it.ITEM_CODE
    FROM
        ITEM it
    INNER JOIN STOCK st
        ON it.ITEM_CODE = st.ITEM_CODE
    WHERE
        it.STOCK_MANAGEMENT_TYPE = 1
    GROUP BY 
        it.ITEM_CODE
    HAVING
        MAX(IFNULL(st.LAST_DELIVERY_DATE, '2999-12-31')) <= DATE ('2023-06-01', '-6 month') AND
        SUM(st.ACTUAL_AMT) = 0
)
DELETE FROM ITEM
WHERE ITEM_CODE IN (SELECT ITEM_CODE FROM DELETE_ITEM);