Help us understand the problem. What is going on with this article?

MySQL: INSERT...ON DUPLICATE KEY UPDATEまとめ

More than 5 years have passed since last update.

INSERT...ON DUPLICATE KEY UPDATE構文を使うと

  • レコードがなければINSERT、あればUPDATE
  • 複数行の一括UPDATE
  • フィールド毎に条件判定して更新

を1度のクエリで行うことができる。集計処理などに便利。

基本

  • レコード(行)がなかったらINSERTあったらUPDATEという処理を1クエリで行える。
    • ユニークなフィールドに対して重複する行が挿入される場合はUPDATEという判定
      • なので利用には UNIQUEインデックス(かPRIMARY KEY)を指定する必要 がある
基本例:aはunique
INSERT INTO
       table (a, b, c)
   VALUES
       (1, 12, 100)
   ON DUPLICATE KEY UPDATE
        b = 20
        , c = 200;

a=1の行がなかった場合

a=1,b=12,c=100 の行が追加

a=1の行が既にあった場合

a=1の行がa=1,b=20,c=200に更新

  • VALUES()関数を使うとINSERTに使おうとした値をUPDATEに使える
VALUES()の例
INSERT INTO
       table (a, b)
   VALUES
       (1, 10)
   ON DUPLICATE KEY UPDATE
        b = b + VALUES(b)
a b
1 5

a b
1 15

複数行の一括UPDATE

複数行追加・更新の例
INSERT INTO
       table (a, b, c)
   VALUES
       (1, 10, 100), (2, 20, 200)
   ON DUPLICATE KEY UPDATE
        b = b + 21
        , c = VALUES(c) + 201;

とすると a=1,a=2 に対して追加/更新が行われる。

a b c
1 5 15

a b c
1 26 301
2 20 200

条件にあうフィールドだけ更新

  • DUPLICATE KEY UPDATE にはCASEやIFが使えるので、フィールド毎に更新条件を設定できる。
    • デフォルト値を使って値を更新されないようにもできる。
取得した数字がレコードの数字より大きければ更新
INSERT INTO
       table (user_id, fb_like, tweet, updated)
   VALUES
       (1, 12, 10, NOW()), (2, 8, 9, NOW()) ... (1000, 42, 30, NOW())
   ON DUPLICATE KEY UPDATE
       fb_like = IF(fb_like > VALUES(fb_like), fb_like, VALUES(fb_like))
       , tweet = IF(tweet > VALUES(tweet), tweet, VALUES(tweet))
        , updated = NOW()

SELECT文

INSERT INTO toppings (id, restaurant_id)
SELECT
       t.id
       , t.restaurant_id
    FROM
       toppings AS t
       LEFT OUTER JOIN pizzas AS p
            ON p.id = t.pizza_id
ON DUPLICATE KEY UPDATE
    restaurant_id = IF(p.restaurant_id > 0, p.restaurant_id, 0) 
yuzroz
Web developer
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away