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