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

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

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

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

基本

• レコード（行）がなかったらINSERTあったらUPDATEという処理を１クエリで行える。
  • ユニークなフィールドに対して重複する行が挿入される場合は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)