DELETE FROM ITEM
WHERE STOCK_MANAGEMENT_TYPE = 1
AND (
NOT EXISTS (
            SELECT
                ITEM_CODE 
            FROM
                STOCK 
            WHERE
                STOCK.ITEM_CODE = ITEM.ITEM_CODE
        )
         OR EXISTS (
            SELECT 
                ITEM_CODE
            FROM
                STOCK
            WHERE
                 STOCK.ITEM_CODE = ITEM.ITEM_CODE
            Group BY ITEM_CODE
            HAVING MAX(IFNULL(LAST_DELIVERY_DATE,'2999-12-31')) <= DATE('2023-06-01','-6 months') and SUM(ACTUAL_AMT) = 0
        )
        );
        
        

SELECT * FROM ITEM;