DELETE
FROM
    ITEM
WHERE
    -- 商品データの在庫管理区分が1
    STOCK_MANAGEMENT_TYPE = 1
    AND (
        -- STOCKにITEM_CODEが存在しない
        NOT EXISTS(
            SELECT
                ITEM_CODE
            FROM
                STOCK
            WHERE
                STOCK.ITEM_CODE = ITEM.ITEM_CODE
        )
        -- STOCKにITEM_CODEが存在する
        -- 商品ごとのLAST_DELIVERY_DATEの最大値が6ヶ月以上前
        -- かつ、ACTUAL_AMTがゼロ
        OR EXISTS(
            SELECT
                ITEM_CODE
            FROM
                STOCK
            WHERE
                STOCK.ITEM_CODE = ITEM.ITEM_CODE
            GROUP BY
                ITEM_CODE
            HAVING
                MAX(IFNULL(LAST_DELIVERY_DATE, '1999-12-31')) <= DATE('2023-06-01', '-6 months')
                AND SUM(ACTUAL_AMT) = 0
        )
        
    
    );