WITH SUB AS(
    SELECT
        ITEM_CODE,
        MAX(LAST_DELIVERY_DATE)
    FROM
        STOCK
    WHERE
        ACTUAL_AMT=0
    GROUP BY
        ITEM_CODE
    HAVING
        MAX(LAST_DELIVERY_DATE) < DATE('2023-06-01','-6 month')
    ), 
    
    DELETE_LIST AS(
    SELECT
        ITEM_CODE
    FROM
        ITEM
    WHERE
        STOCK_MANAGEMENT_TYPE=1
        AND (
            ITEM_CODE NOT IN (SELECT ITEM_CODE FROM STOCK)
            OR ITEM_CODE IN (SELECT ITEM_CODE FROM SUB)
            )
    )

DELETE FROM ITEM
WHERE ITEM_CODE IN (SELECT ITEM_CODE FROM DELETE_LIST);