INSERT … ON DUPLICATE KEY UPDATEを使ったUpdate方法、UpdateとInsertを同時に！？

複数レコードを同時に更新したい！さらに、同時に新しいレコードがあった場合はInsertしたい！と思ったことはありませんか？

例えば、連絡先一覧を表示させたとします。
Aさんの電話番号を変更　そして、新しくGさんの連絡先を追加しました。
このような時には、通常Aさんの連絡先のUpdateとGさんの連絡先のInsertを行わなければいけません。

このような時に、「INSERT … ON DUPLICATE KEY UPDATE」を使えば１クエリーで済ますことが出来ます。

先程まで利用していたレコードに、「ID,NAME,AGE」をINSERTしてみます。もし、鍵(この場合「ID」)が重複していた場合はNAMEとAGEをUpdateしてくださいという文を作成してみましょう。

mysql> INSERT INTO table01 (ID,NAME,AGE)
    -> VALUES
    -> (1,'かつお',15),
    -> (2,'わかめ',23),
    -> (3,'たらお',8)
    -> ON DUPLICATE KEY UPDATE
    -> NAME = VALUES(NAME),
    -> AGE = VALUES(AGE)
    -> ;
Query OK, 6 rows affected (0.00 sec)
Records: 3  Duplicates: 3  Warnings: 0

mysql>

上記は、KEYが重複している場合は、UDATEしてください。
UPDATEするときはNAMEはVALUESで指定したNAME、AGEはVALUESで指定したAGEでお願いしますーというSQLになっています。

実行結果は以下のようになります。

mysql> select * from table01;
+----+-----------+------+---------------------+
| ID | NAME      | AGE  | UPDATE_TIME         |
+----+-----------+------+---------------------+
|  1 | かつお    |   15 | 2014-05-18 14:29:12 |
|  2 | わかめ    |   23 | 2014-05-18 14:29:12 |
|  3 | たらお    |    8 | 2014-05-18 14:29:12 |
+----+-----------+------+---------------------+
3 rows in set (0.00 sec)

mysql>

INSERTクエリーを投げているにもかかわらず、あたらしいレコードがInsertされずに、既存のレコードが更新されていることがわかります。

UpdateとInsertを同時に１クエリーで行う

では、NAMEとAGEを更新しながらも、新しいレコードとしてサザエさんを追加してみましょう。サザエさんを追加する時に、KEYであるIDには「”」のようにしてIDを指定しません。そうすると自動でAUTO_INCREMENTされたIDがINSERTされます。

mysql> INSERT INTO table01 (ID,NAME,AGE)
    -> VALUES
    -> (1,'かつおくん',18),
    -> (2,'わかめちゃん',80),
    -> (3,'たらおくん',20),
    -> ('','サザエさん',24)
    -> ON DUPLICATE KEY UPDATE
    -> NAME = VALUES(NAME),
    -> AGE = VALUES(AGE)
    -> ;
Query OK, 7 rows affected, 1 warning (0.00 sec)
Records: 4  Duplicates: 3  Warnings: 0

mysql>

結果を見てみますと、見事に存在するID分はUpdateされ、存在しない(=指定しない)ID分はINSERTされたことがわかると思います。

mysql> select * from table01;
+----+--------------------+------+---------------------+
| ID | NAME               | AGE  | UPDATE_TIME         |
+----+--------------------+------+---------------------+
|  1 | かつおくん         |   18 | 2014-05-18 14:49:04 |
|  2 | わかめちゃん       |   80 | 2014-05-18 14:49:04 |
|  3 | たらおくん         |   20 | 2014-05-18 14:49:04 |
|  4 | サザエさん         |   24 | 0000-00-00 00:00:00 |
+----+--------------------+------+---------------------+
4 rows in set (0.00 sec)

mysql>

今回この記事を書く上で分かったこと

1. MySQLは、Update前の値とUpdate後の値が同じ場合はUpdateしない(*1)
2. UpdateクエリーにCASE文を使うと複数レコードを同時にUpdate出来る
3. InsertクエリーにON DUPLICATE KEY UPDATEを利用すると、UpdateとInsertを１クエリーで行える

*1: MySQLはUpdate前とUpdate後の値を比較し、それらが同じ場合、Updateは行わない。

もし現在カラムが持つ値に設定するなら、MySQL はそれに気づくので更新はしません。
If you set a column to the value it currently has, MySQL notices this and does not update it.
http://dev.mysql.com/doc/refman/5.1/ja/update.html

参考：
MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query ・・・InsertとUpdateを同時に行う方法(英語)
MySql update multiple rows in single query ・・・Case文を使ったUpdate方法(英語)