MySQL 9 min read

MySQLのUPDATE文 ― JOIN・LIMIT・CASEの書き方とERROR 1175/1093の回避

MySQL の UPDATE は、基本の UPDATE 表 SET 列 = 値 WHERE 条件 のほかに、ORDER BY と LIMIT、複数の表の JOIN も書けます。この記事の出力は MySQL 8.4.6 で採りました。

まず見る（結論）

  • 実行前に、同じ WHERE で SELECT して対象の行を確かめる。autocommit が既定で ON なので、UPDATE は実行した時点で確定する
  • 結果の Changed: 0 は「該当行はあったが、値が同じだった」という意味
  • ERROR 1175 は safe update モードで、キー列を使った WHERE が無いときに出る
  • ERROR 1093 は更新する表を同じ文のサブクエリで読んだときに出る。サブクエリを派生表でもう一段くるむと通る

基本と結果の読み方

mysql> UPDATE customers SET name='x' WHERE id=1;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE customers SET name='x' WHERE id=1;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Rows matched は WHERE に一致した行の数、Changed は実際に値が変わった行の数です。アプリケーションで「更新件数が0なら行が無い」と判定していると、値が同じだったときに誤判定します。

間違えたら ROLLBACK できるか

mysql クライアントの既定は autocommit=1 で、UPDATE は1文ごとに確定します。戻せるようにするには、先に START TRANSACTION を打ちます。

mysql> SELECT @@autocommit;      -- 1
mysql> START TRANSACTION;
mysql> UPDATE orders SET amount=9999.99 WHERE id=1;
mysql> SELECT id, amount FROM orders WHERE id=1;   -- 9999.99
mysql> ROLLBACK;
mysql> SELECT id, amount FROM orders WHERE id=1;   -- 100.00 に戻った

件数を絞る（ORDER BY と LIMIT）

id の大きい方から2行だけ更新する例です。

mysql> UPDATE customers SET name=CONCAT(name, '_tail') ORDER BY id DESC LIMIT 2;
Query OK, 2 rows affected (0.14 sec)
Rows matched: 2  Changed: 2  Warnings: 0

id 4 の cat と id 5 の d が cat_tail、d_tail になりました。大量の行を少しずつ更新したいときに使えます。ただし、JOIN を使う複数表の UPDATE には ORDER BY も LIMIT も書けません。

別の表と JOIN して更新する

顧客の名前を条件に、注文の金額を1.1倍にします。UPDATE の後ろに JOIN を書き、SET では更新する表の別名を付けます。

mysql> UPDATE orders o
    ->   JOIN customers c ON o.customer_id = c.id
    ->    SET o.amount = o.amount * 1.1
    ->  WHERE c.name LIKE '%a%';
Query OK, 2 rows affected (0.21 sec)
Rows matched: 2  Changed: 2  Warnings: 0

名前に a を含む2人（cat_tail、d_tail）の注文が、50.00 → 55.00、600.00 → 660.00 になりました。

行ごとに違う値を入れる（CASE）

mysql> UPDATE orders
    ->    SET amount = CASE id
    ->        WHEN 1 THEN 111.11
    ->        WHEN 2 THEN 222.22
    ->        WHEN 3 THEN 333.33
    ->        ELSE amount
    ->    END
    ->  WHERE id IN (1, 2, 3);
Query OK, 3 rows affected (0.09 sec)
Rows matched: 3  Changed: 3  Warnings: 0

WHERE を付け忘れると全行が対象になり、WHEN に無い行は ELSE amount（元の値のまま）になります。ELSE も書き忘れると、それらの行は NULL になります。

ERROR 1175（safe update モード）

mysql クライアントを --safe-updates（-U）付きで起動していると、キー列を使った WHERE が無い UPDATE は拒否されます。

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

WHERE が無いときだけでなく、索引の無い列（この例では name）だけで絞ったときも同じエラーでした。全行の更新を防ぐための設定です。意図した更新なら主キーで絞るか、そのセッションだけ SET SQL_SAFE_UPDATES = 0; にします。

ERROR 1093（同じ表をサブクエリで読む）

「id が最大の行を更新する」をそのまま書くとエラーになります。

mysql> UPDATE orders SET amount=0 WHERE id=(SELECT MAX(id) FROM orders);
ERROR 1093 (HY000): You can't specify target table 'orders' for update in FROM clause

MySQL は、更新している表を同じ文のサブクエリで直接読むことを許していません。サブクエリをもう一段くるんで派生表にすると、先に結果が作られるので通ります。

mysql> UPDATE orders SET amount=0
    ->  WHERE id=(SELECT max_id FROM (SELECT MAX(id) AS max_id FROM orders) AS t);
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

この例なら UPDATE orders SET amount=0 ORDER BY id DESC LIMIT 1; とも書けます。

RELATED MySQL