DELETE FROM
        ITEM
    WHERE
        -- 在庫管理区分が在庫管理するもののみ
        STOCK_MANAGEMENT_TYPE = 1
    AND (
        NOT EXISTS
            (
                SELECT
                        I.ITEM_CODE
                FROM
                   STOCK 
                WHERE
                    ITEM.ITEM_CODE = STOCK.ITEM_CODE
            )
        OR EXISTS
            (
                SELECT
                        ITEM_CODE
                    FROM
                       STOCK
                    WHERE
                        ITEM.ITEM_CODE = STOCK.ITEM_CODE
                    GROUP BY
                        ITEM_CODE
                    HAVING
                        -- 在庫合計の実在庫数が0
                        SUM(ACTUAL_AMT) = 0
                        -- 最後に更新された出荷日が6ヶ月前
                    AND MAX(IFNULL(LAST_DELIVERY_DATE, '2999-12-31')) <= DATE ('2023-06-01', '-6 months') 
            )
    )
;