MySQL - UPDATE
概要
UPDATE文は、MySQLのテーブルに格納された既存レコードを修正するためのSQL文である。
WHERE 句を使用して更新対象の行を指定し、WHERE句を省略すると全行が更新される。
UPDATE文はトランザクション内で実行可能であり、コミット前であればロールバックにより更新を取り消すことができる。
単一テーブルの更新だけでなく、複数テーブルの同時更新、サブクエリを使用した条件指定、CASE文による条件分岐更新等、柔軟な更新操作が可能である。
単一テーブル更新では ORDER BY 句と LIMIT 句を組み合わせることで、特定の順序で限られた数の行を更新できる。
また、大量のデータを更新する場合は、パフォーマンスとロックの影響を考慮し、バッチ更新やインデックスの活用を検討する必要がある。
更新操作では、SET句で指定したカラムとインデックスの両方が更新されるため、不要なインデックスは削除することで性能向上を図ることができる。
外部キー制約が設定されている場合は、参照整合性を維持するために、更新時の動作 (CASCADE、SET NULL、RESTRICT) を適切に設定することが重要である。
MySQLのセーフモード (sql_safe_updates) を有効にすることにより、WHERE句なし または キー列を使用しないUPDATE文を防止し、意図しないデータ更新を防ぐことができる。
UPDATE文と類似の更新系文として、REPLACE文 (削除後に挿入)、INSERT ON DUPLICATE KEY UPDATE文 (重複時に更新) が存在するが、それぞれ動作が異なるため、用途に応じて適切に使い分けることが必要である。
基本構文
単一テーブルのUPDATE
単一テーブルのUPDATE文の構文は以下の通りである。
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
[PARTITION (partition_name [, partition_name] ...)]
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
基本的な使用例を以下に示す。
# 特定の行を更新
UPDATE products SET price = 100 WHERE id = 5;
# 複数のカラムを同時に更新
UPDATE users SET status = 'active', last_login = NOW() WHERE id = 100;
# 全ての行を更新 (WHERE句なし)
UPDATE temp_table SET processed = 1;
SET句では、複数のカラムを同時に更新できる。
この場合、代入は左から右へ順に評価される。
# カラムの値を参照した更新
UPDATE items SET col1 = col1 + 1, col2 = col1;
# col1が増加された後、col2にはその新しい値が割り当てられる
DEFAULT キーワードを使用して、カラムのデフォルト値を設定できる。
# デフォルト値を設定
UPDATE products SET price = DEFAULT WHERE id = 5;
修飾子
UPDATE文では、下表に示す修飾子を使用して更新動作を制御できる。
| 修飾子 | 説明 |
|---|---|
| LOW_PRIORITY | テーブルを読み取り中の他のクライアントがいなくなるまで、 UPDATEの実行を遅延させる。 MyISAM、MEMORY、MERGEストレージエンジンで有効 |
| IGNORE | 更新中にエラーが発生しても無視して処理を継続する。 ユニークキーの重複が発生した場合、その行は更新されない。 |
使用例を以下に示す。
# 低優先度で更新を実行
UPDATE LOW_PRIORITY products SET price = 100 WHERE category_id = 5;
# エラーを無視して更新を継続
UPDATE IGNORE users SET email = 'new@example.com' WHERE status = 'active';
PARTITION句
パーティション化されたテーブルでは、PARTITION 句を使用して特定のパーティションからのみ更新できる。
# 特定のパーティションから更新
UPDATE employees PARTITION (p0)
SET store_id = 2
WHERE fname = 'Jill';
# 複数パーティションを指定
UPDATE sales PARTITION (p_2023_q1, p_2023_q2)
SET processed = 1
WHERE amount > 1000;
※注意
指定されたパーティションが存在しない場合、ステートメントはエラーで失敗する。
条件付き更新
WHERE句の基本
WHERE句を使用して、更新対象の行を指定する。
WHERE句を省略すると、テーブルの全行が更新される。
下表に、基本的な比較演算子を示す。
| 演算子 | 説明 |
|---|---|
| = | 等しい |
| <>, != | 等しくない |
| < | より小さい |
| > | より大きい |
| <= | 以下 |
| >= | 以上 |
使用例を以下に示す。
# 等価条件
UPDATE products SET price = 100 WHERE id = 5;
# 不等号条件
UPDATE products SET discount = 0.1 WHERE price > 1000;
# 不等価条件
UPDATE orders SET status = 'pending' WHERE status <> 'completed';
複数条件の指定
AND、OR、NOTを使用して、複数の条件を組み合わせることができる。
# AND条件 (両方の条件を満たす行を更新)
UPDATE products
SET discount = 0.1
WHERE category = 'electronics' AND price > 1000;
# OR条件 (いずれかの条件を満たす行を更新)
UPDATE products
SET status = 'inactive'
WHERE last_purchase IS NULL
OR last_purchase < DATE_SUB(NOW(), INTERVAL 1 YEAR);
# NOT条件 (条件を満たさない行を更新)
UPDATE products
SET featured = 1
WHERE NOT status = 'inactive';
# IN句 (リストに含まれる値を持つ行を更新)
UPDATE orders
SET status = 'shipped'
WHERE order_id IN (101, 102, 103, 104);
# BETWEEN句 (範囲指定)
UPDATE sales
SET commission = salary * 0.15
WHERE hire_date BETWEEN '2020-01-01' AND '2021-12-31';
# LIKE句 (パターンマッチング)
UPDATE customers
SET notification_preference = 'email'
WHERE email LIKE '%@gmail.com';
# IS NULL (NULL値の更新)
UPDATE products
SET description = 'No description available'
WHERE description IS NULL;
# 複合条件の例
UPDATE inventory
SET alert_level = 'low'
WHERE (quantity < 50 AND product_type = 'essential')
OR (quantity < 10 AND product_type = 'standard');
ORDER BYとLIMITの組み合わせ
ORDER BY 句 と LIMIT 句を組み合わせることで、特定の順序で限られた数の行を更新できる。
※注意
ORDER BY句とLIMIT句は、単一テーブルUPDATEでのみ使用可能である。
複数テーブルのUPDATEでは使用できない。
# 最も最近雇用された5名の給与を5%引き上げる
UPDATE employees
SET salary = salary * 1.05
ORDER BY hire_date DESC
LIMIT 5;
# 優先度の高いタスクから10件を完了状態にする
UPDATE tasks
SET status = 'completed'
WHERE priority = 'high'
ORDER BY created_date DESC
LIMIT 10;
# 古いデータから1000件ずつ処理
UPDATE archive_data
SET processed = 1
ORDER BY created_date ASC
LIMIT 1000;
この機能は、大量のデータを段階的に更新する際に有用である。
一度に大量の行を更新するとロックやリソース消費が問題になるため、LIMIT句を使用してバッチ更新を行うことが推奨される。
複数テーブルの更新
MySQLでは、複数のテーブルを同時に更新することができる。
これは、JOIN句を使用して実現される。
INNER JOINを使用した更新
INNER JOIN 句を使用して、関連するテーブルを同時に更新する。
# 複数テーブルUPDATEの基本構文
UPDATE t1
INNER JOIN t2 ON t1.id = t2.t1_id
SET t1.col1 = value1, t2.col2 = value2
WHERE <条件>;
# 使用例：商品のカテゴリ名を更新
UPDATE products p
INNER JOIN categories c ON p.category_id = c.id
SET p.category_name = c.name
WHERE c.status = 'active';
# 複数テーブルの同時更新
UPDATE t1, t2
SET t1.col1 = t2.col1, t2.col2 = expr
WHERE t1.id = t2.id;
LEFT JOINを使用した更新
LEFT JOIN 句を使用して、左テーブルの全行を評価しながら更新できる。
# 顧客の最終注文日を更新
UPDATE customers c
LEFT JOIN orders o ON c.id = o.customer_id
SET c.last_order_date = o.order_date
WHERE o.order_date IS NOT NULL
OR c.last_order_date IS NULL;
# 左テーブルの全行が評価される
# 一致しない場合、右テーブルのカラムはNULLになる
JOINの型と動作の違いを下表に示す。
| JOIN型 | 説明 | 対象行 |
|---|---|---|
| INNER JOIN | 両テーブルでマッチするレコードのみ | マッチするレコードのみ更新 |
| LEFT JOIN | 左テーブルの全レコード | 左テーブル全行を評価、右テーブルはマッチしない場合NULL |
サブクエリを使用した更新
サブクエリを使用して、別のテーブルの値に基づいて更新を行うことができる。
SET句でのサブクエリ使用
SET句でサブクエリを使用することで、他のテーブルの値を参照して更新できる。
# 各従業員の給与を部門の平均給与に設定
UPDATE employees
SET salary = (
SELECT AVG(salary)
FROM salary_benchmarks
WHERE department_id = employees.department_id
)
WHERE hire_date >= '2020-01-01';
# 複数カラムの更新
UPDATE sales_summary
SET (total_amount, num_transactions) = (
SELECT SUM(amount), COUNT(*)
FROM transactions
WHERE month = sales_summary.month
)
WHERE year = 2024;
WHERE句でのサブクエリ使用
WHERE句でサブクエリを使用して、更新対象の行を動的に決定できる。
# IN演算子を使用
UPDATE orders
SET status = 'verified'
WHERE customer_id IN (
SELECT id
FROM customers
WHERE credit_score >= 750
);
# NOT IN演算子を使用
UPDATE products
SET discount = 0.2
WHERE id NOT IN (
SELECT product_id
FROM top_sellers
WHERE year = YEAR(NOW())
);
# EXISTS演算子を使用
UPDATE customers
SET loyalty_tier = 'gold'
WHERE EXISTS (
SELECT 1
FROM orders
WHERE customer_id = customers.id
AND order_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
AND total_amount > 10000
);
同一テーブルを参照する場合の制限と回避策
一般的に、同じテーブルを更新しながら、その同じテーブルをサブクエリで参照することはできない。
# エラーになる例
UPDATE products
SET price = price * 1.1
WHERE id IN (
SELECT id FROM products WHERE sales > 100
);
# エラーメッセージ:
# ERROR 1093 (HY000): You can't specify the target table for update in the FROM clause
回避策として、派生テーブルを使用する。
# 派生テーブルを使用した回避策 (推奨)
UPDATE products p,
(SELECT id FROM products WHERE sales > 100) AS top_sellers
SET p.price = p.price * 1.1
WHERE p.id = top_sellers.id;
# 一時テーブルを使用した回避策
CREATE TEMPORARY TABLE temp_ids AS
SELECT id FROM products WHERE sales > 100;
UPDATE products
SET price = price * 1.1
WHERE id IN (SELECT id FROM temp_ids);
DROP TEMPORARY TABLE temp_ids;
# セルフジョインを使用した回避策
UPDATE products p1
INNER JOIN products p2 ON p1.id = p2.id
SET p1.price = p1.price * 1.1
WHERE p2.sales > 100;
CASE文を使用した条件分岐更新
CASE式を使用することで、条件に応じて異なる値を設定できる。
単一カラムの条件分岐更新
CASE式の基本構文を以下に示す。
UPDATE table_name
SET column = CASE
WHEN condition1 THEN value1
WHEN condition2 THEN value2
...
ELSE default_value
END
WHERE where_condition;
使用例を以下に示す。
# 成績に応じてグレードを設定
UPDATE students
SET grade = CASE
WHEN total_marks >= 450 THEN 'A'
WHEN total_marks >= 350 AND total_marks < 450 THEN 'B'
WHEN total_marks >= 300 AND total_marks < 350 THEN 'C'
WHEN total_marks >= 200 THEN 'D'
ELSE 'F'
END
WHERE exam_date = '2024-01-15';
複数カラムの同時条件分岐更新
複数のカラムに対して、それぞれ異なるCASE式を適用できる。
# 給与とボーナスを同時に更新
UPDATE employees
SET salary = CASE
WHEN department = 'Engineering' THEN salary * 1.15
WHEN department = 'Sales' THEN salary * 1.10
WHEN department = 'HR' THEN salary * 1.05
ELSE salary
END,
bonus = CASE
WHEN performance_rating >= 4.5 THEN salary * 0.20
WHEN performance_rating >= 3.5 THEN salary * 0.10
ELSE salary * 0.05
END
WHERE hire_date <= DATE_SUB(NOW(), INTERVAL 1 YEAR);
推奨事項を以下に示す。
- 常にELSE句を含める
- 予期しないケースやNULL値に対応する。
- 最も可能性の高い条件を最初に記述
- クエリパフォーマンスを向上させる。
- CASE文が複雑になりすぎる場合
- プログラムロジックで処理を検討する。
- CASE式内の条件は相互排他的に設計
- 条件が重複しないようにする。
外部キー制約と更新
外部キー制約が設定されている場合、更新時の動作を制御するアクションを指定できる。
ON UPDATE CASCADE
親レコード更新時に、子レコードも自動的に更新される。
# 外部キー制約の定義
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE CASCADE
) ENGINE=INNODB;
# 親レコードを更新すると、子レコードも自動更新される
UPDATE parent SET id = 100 WHERE id = 1;
# 結果:
# child テーブルの parent_id が 1 の全ての行が、自動的に parent_id = 100 に更新される
ON UPDATE SET NULL
親レコード更新時に、子レコードの外部キー列が NULL に設定される。
# 外部キー制約の定義
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE SET NULL
) ENGINE=INNODB;
# 親レコードを更新すると、子レコードの外部キーがNULLになる
UPDATE parent SET id = 100 WHERE id = 1;
※注意
外部キー列がNOT NULL制約を持つ場合、SET NULLは使用できない。
- 正しい定義を以下に示す。
parent_id INT,
- 誤った定義 (エラーが発生)
parent_id INT NOT NULL,
ON UPDATE RESTRICT / NO ACTION
親テーブルの更新操作を拒否する。
子テーブルに一致するレコードが存在する場合、エラーが発生する。
FOREIGN KEY (parent_id)
REFERENCES parent(id)
ON UPDATE RESTRICT
デフォルト動作として、ON UPDATEが指定されない場合、デフォルトは NO ACTION (RESTRICT相当) である。
外部キー制約の一時的な無効化
データの一括更新等で外部キーチェックを一時的に無効化する場合、以下に示すコマンドを使用する。
外部キーチェックを無効にすると、データの整合性が失われる可能性があるため、使用には十分注意が必要である。
# 外部キーチェックを無効化
SET FOREIGN_KEY_CHECKS = 0;
# 更新操作を実行
UPDATE parent_table SET column = value WHERE condition;
UPDATE child_table SET column = value WHERE condition;
# 外部キーチェックを再度有効化
SET FOREIGN_KEY_CHECKS = 1;
セーフモード
MySQLのセーフモード (sql_safe_updates) は、WHERE句なし または キー列を使用しないUPDATE文を防止する。
セーフモードが有効な場合、以下のような更新はエラー (エラーコード1175) になる。
# WHERE句なしの更新 (エラーになる)
UPDATE products SET price = 100;
# キー列を使用しない更新 (エラーになる)
UPDATE products SET price = 100 WHERE category = 'electronics';
セーフモードの設定を変更する方法を以下に示す。
# セーフモードを無効化
SET SQL_SAFE_UPDATES = 0;
# 更新を実行
UPDATE products SET price = 100 WHERE category = 'electronics';
# セーフモードを再度有効化
SET SQL_SAFE_UPDATES = 1;
成功する例を以下に示す。
# PRIMARY KEYのidカラムを使用
UPDATE products SET price = 100 WHERE id = 5;
# LIMIT句を使用
UPDATE products SET price = 100 LIMIT 10;
※注意
セーフモードは、意図しないデータ更新を防ぐための重要な機能である。
一時的に無効化する場合は、操作後に必ず再度有効化することが推奨される。
更新結果の確認
UPDATE文の実行結果は、影響を受けた行数として表示される。
UPDATE products SET price = 100 WHERE category_id = 5;
# 出力例： Query OK, 15 rows affected (0.01 sec)
ROW_COUNT 関数を使用して、直前のUPDATE文で更新された行数を取得できる。
UPDATE employees SET salary = salary * 1.05 WHERE department = 'Sales';
SELECT ROW_COUNT();
# 出力例 # +--------------+ # | ROW_COUNT() | # +--------------+ # | 15 | # +--------------+
※注意
- ROW_COUNT関数は、直後のSELECT文で実行する必要がある。
- 他のSQL文を実行すると、値がリセットされる。
- UPDATEでは、新しい値が古い値と同じ場合、その行は影響を受けたものとしてカウントされない。
- 実際に変更された行数のみがカウントされる。
- CLIENT_FOUND_ROWSフラグを使用すると、マッチした行数を返す。
- アプリケーションレベルで接続フラグを設定する必要がある。
パフォーマンス最適化
インデックスの活用
UPDATE文のパフォーマンスを向上させるために、WHERE句で使用する列にインデックスを作成する。
# インデックスが存在しない場合、全行スキャンが発生する
UPDATE products SET price = 100 WHERE category_id = 5;
# インデックスを作成
CREATE INDEX idx_category_id ON products(category_id);
# インデックスを使用した高速更新
UPDATE products SET price = 100 WHERE category_id = 5;
EXPLAIN 文を使用して、UPDATE文の実行計画を確認できる。
# 実行計画を確認
EXPLAIN UPDATE products SET price = 100 WHERE category_id = 5;
インデックスの効果と注意点を以下に示す。
正の効果として、以下が挙げられる。
- WHERE句で指定されるカラムがインデックスされていると、必要な行をすぐに特定できる。
- 更新対象の行が限定されている場合、大幅にパフォーマンスを向上させる。
負の効果として、以下が挙げられる。
- 更新対象のカラムがインデックス付きの場合、そのインデックスも更新される必要がある。
- インデックスが多いと、UPDATE操作のオーバーヘッドが増加する。
- 不要なインデックスは削除することで、パフォーマンス向上を期待できる。
大量データの更新
大量のデータを1度に更新すると、以下に示す問題が発生する可能性がある。
- テーブルロックによる他のクエリの待機
- トランザクションログの肥大化
- 長時間のロック保持によるデッドロック
- メモリとCPUリソースの大量消費
推奨される大量データ更新の方法を以下に示す。
バッチ更新 (チャンク更新)
LIMIT 句を使用して、少しずつ更新を行う。
# 1000件ずつ更新を繰り返す
UPDATE large_table
SET status = 'processed'
WHERE status = 'pending'
ORDER BY id ASC
LIMIT 1000;
# 更新対象がなくなるまで繰り返す
スクリプトでのバッチ更新の例を以下に示す。
#!/usr/bin/env sh
while true; do
affected=$(mysql -u user -p'password' -D database -N -B -e \
"UPDATE orders SET status = 'processed' \
WHERE status = 'pending' LIMIT 1000; \
SELECT ROW_COUNT();")
echo "Updated $affected rows"
if [ "$affected" -eq 0 ]; then
break
fi
sleep 1
done
バッチ更新のメリットを以下に示す。
- ロック期間を短縮できるため、他のトランザクションへの影響を最小化
- メモリ使用量を削減
- 処理の進捗を監視しやすい
トランザクションの制御
大量更新時は、トランザクションを短く保つことが重要である。
# 自動コミットを有効にする (各UPDATE文が自動的にコミットされる)
SET AUTOCOMMIT = 1;
# バッチ更新を実行
UPDATE large_table SET status = 'processed' WHERE id <= 1000;
更新後の最適化
大量のデータを更新した後は、OPTIMIZE TABLE 文でテーブルを最適化する。
# テーブルの最適化 (断片化の解消、領域の再利用)
OPTIMIZE TABLE products;
UPDATEと他の更新系の違い
REPLACE文との違い
REPLACE文は、既存の行を削除してから新しい行を挿入する。
REPLACE INTO products (id, name, price)
VALUES (1, 'New Product', 100);
REPLACE文とUPDATE文の比較を下表に示す。
| 項目 | REPLACE | UPDATE |
|---|---|---|
| 操作内容 | 削除 → 挿入 | 現地修正 |
| 自動増分 | 新しいIDを割り当て | 変更なし |
| 外部キー | カスケード削除が発生 | 対象行のみ修正 |
| インデックス | 再構築される | 既存のまま |
| パフォーマンス | 遅い (削除+挿入) | 速い (現地修正) |
REPLACE使用時の注意点を以下に示す。
- 指定しないカラムはデフォルト値に設定される
- デフォルト値がない場合はエラーになる。
- 外部キー制約によるカスケード削除が発生する可能性
- ON DELETE CASCADEが設定されている場合、子レコードも削除される。
- トリガーが複数回呼び出される
- 削除と挿入それぞれでトリガーが実行される。
INSERT ON DUPLICATE KEY UPDATEとの違い
INSERT ON DUPLICATE KEY UPDATE は、UNIQUEキーまたはPRIMARY KEYで重複が発生した場合、UPDATEを実行する。
INSERT INTO products (id, name, price)
VALUES (1, 'Product', 100)
ON DUPLICATE KEY UPDATE
price = VALUES(price),
updated_at = NOW();
3つの更新方法の比較を下表に示す。
| 項目 | UPDATE | REPLACE | INSERT ON DUPLICATE KEY UPDATE |
|---|---|---|---|
| 行が存在しない場合 | 何もしない | 挿入 | 挿入 |
| 行が存在する場合 | 更新 | 削除 → 挿入 | 更新 |
| 自動増分の扱い | 変更なし | 新IDを割り当て | INSERTパスで新ID、UPDATE時は変更なし |
| パフォーマンス | 速い | 遅い | 速い |
| トリガー発火 | 1回 (UPDATE) | 2回 (DELETE、INSERT) | 1から2回 |
| 外部キー影響 | 最小限 | カスケード削除可能 | 最小限 |
各方法の使い分けを以下に示す。
- UPDATE
- 既存行を確実に更新したい場合に使用する。
- REPLACE
- 古いデータを完全に削除して置き換えたい場合に使用する (非推奨、INSERT ON DUPLICATE KEY UPDATE推奨)。
- INSERT ON DUPLICATE KEY UPDATE
- 存在しなければ挿入、存在すれば更新 (upsert操作) に使用する。
実装例を以下に示す。
# ユーザーが初めてログインしたら行を挿入、
# 2回目以降はログイン情報を更新
INSERT INTO user_profiles (user_id, last_login, login_count)
VALUES (123, NOW(), 1)
ON DUPLICATE KEY UPDATE
last_login = NOW(),
login_count = login_count + 1;
