PHPからPOSTされてくるデーターを元に、１テーブル内の複数レコードを１クエリーでUpdateする方法がないかと模索した。と言うのも、たとえば1ヶ月のデーターを更新させるときに、30日分のレコードを各レコードごとにUpdateしていたらかっこ悪いなと。だったら、１クエリーを用いて複数のレコードを更新させようと思ったわけだ。
今回、複数レコードのUpdateを１クエリーで行う方法、さらには複数レコードのUpdateとInsertを同時に１クエリーで行う方法を記載しておきます。結構知らない方が多いと思うので、面白いと思った際には色々な方法でシェアしてくれると嬉しいです！
答えに急ぎたい方は以下のリンクをクリックしてください。でも、できればゆっくりこのページもざっと読んでくれると嬉しいな。
次のページ：Case文を使った複数レコードのUpdate方法
次の次のページ：INSERT … ON DUPLICATE KEY UPDATEを使ったUpdate方法、UpdateとInsertを同時に！？
今回、動作を確認するために以下のようなテーブルを作成した。
SQLコマンドが小文字だったり大文字だったりするけど、そこは愛嬌として見逃してください。
検証用テーブルの用意
mysql> create table table01 (
-> ID INT(11) NOT NULL AUTO_INCREMENT Primary KEY,
-> NAME VARCHAR(30),
-> AGE int(3),
-> UPDATE_TIME TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> );
Query OK, 0 rows affected (0.20 sec)
mysql>
mysql> desc table01;
+-------------+-------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------------------+-----------------------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | varchar(30) | YES | | NULL | |
| AGE | int(3) | YES | | NULL | |
| UPDATE_TIME | timestamp | NO | | 0000-00-00 00:00:00 | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+---------------------+-----------------------------+
4 rows in set (0.00 sec)
mysql>
これによって、「ID」を自動でカウントアップ(AUTO_INCREMENT)される主キーとして、「NAME」と「AGE」を収めるテーブルを作成できました。「UPDATE_TIME」には、「on update CURRENT_TIMESTAM」とあるとおり、Updateを行ったときに自動でその時刻が挿入されるテーブルとなっています。
検証用レコードの用意
例となるレコードをいくつか投入してみます。今回はなんとなく頭の中で「サザエさん」と出てきたので、カツオ、ワカメとタラオの年齢をテーブルに挿入してみました。ちなみに彼らの年齢はフジテレビのサイトを参考にさせて頂きました。
mysql> INSERT INTO table01 (NAME,AGE) VALUES ('カツオ',11),('ワカメ',9),('タラオ',3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
INSERTを行う場合は、上記のとおり１クエリーで複数のレコードを投入できることはご存知でしたでしょうか？私は今回はじめて知りました。便利ですね。INSERTされたレコードを乱暴なSELECT文で確認してみましょう。
mysql> select * from table01; +----+-----------+------+---------------------+ | ID | NAME | AGE | UPDATE_TIME | +----+-----------+------+---------------------+ | 1 | カツオ | 11 | 0000-00-00 00:00:00 | | 2 | ワカメ | 9 | 0000-00-00 00:00:00 | | 3 | タラオ | 3 | 0000-00-00 00:00:00 | +----+-----------+------+---------------------+ 3 rows in set (0.00 sec) mysql>
これで検証用のテーブルとレコードを用意できました。
複数レコードのUpdate実施
それでは、この状態で１年経った年、それぞれのAGEを１つ追加してみましょう。
mysql> UPDATE table01 set AGE=AGE+1;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
mysql>
mysql> select * from table01;
+----+-----------+------+---------------------+
| ID | NAME | AGE | UPDATE_TIME |
+----+-----------+------+---------------------+
| 1 | カツオ | 12 | 2014-05-18 13:26:29 |
| 2 | ワカメ | 10 | 2014-05-18 13:26:29 |
| 3 | タラオ | 4 | 2014-05-18 13:26:29 |
+----+-----------+------+---------------------+
3 rows in set (0.00 sec)
mysql>
ここになって気づきましたが、例があまりよくなかったかもですね。年齢を１つ追加するというのには条件が必要ないので、条件を指定しないUpdateクエリーで複数のレコードを更新することが出来てしまいました(^。^;)
複数レコードのUpdate やり直し(条件付きの場合)
気を取り直しまして、すべての名前をカタカナから平仮名に変更するUpdateクエリーはどのようになるでしょうか。この場合は前述の方法ではうまくいきません。IDもしくは現在のNAMEやAGEを元にした条件にそって、NAMEをカタカナから平仮名に変更する必要があります。今回はNAMEを条件式WHEREに利用してクエリーを実行してみます。３人分のUpdateクエリーを３回実行して変更します。
mysql> UPDATE table01 SET NAME='かつお' WHERE NAME='カツオ'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE table01 SET NAME='わかめ' WHERE NAME='ワカメ'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE table01 SET NAME='たらお' WHERE NAME='タラオ'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> mysql> select * from table01; +----+-----------+------+---------------------+ | ID | NAME | AGE | UPDATE_TIME | +----+-----------+------+---------------------+ | 1 | かつお | 12 | 2014-05-18 13:30:56 | | 2 | わかめ | 10 | 2014-05-18 13:31:13 | | 3 | たらお | 4 | 2014-05-18 13:31:26 | +----+-----------+------+---------------------+ 3 rows in set (0.00 sec) mysql>
「PHPだったら、変更前の名前と変更後の名前を配列に入れて、それぞれでSQL文を作成してUpdateクエリーをかけるから、特に難しくないし、いいじゃん」という考えもあると思うのですが、これが数千数万のレコードのUpdateであった場合、数千数万クエリーで実施するより、１クエリーで処理出来たほうがいいですよね？
今回、Google検索を重ねた結果、複数のレコードをUpdateする方法を２つ見つけました！さらに、以下の２番目の方法では１クエリーでUpdateとInsertが同時に行えます。
- Updateクエリー内でCASE分を用い、条件によってUpdateする値を切り替える
- Insertクエリー内で、ON DUPLICATE KEYUPDATE を用いる
各ページにてその方法を例と共に共有したいと思います！
次のページ：Case文を使った複数レコードのUpdate方法
次の次のページ：INSERT … ON DUPLICATE KEY UPDATEを使ったUpdate方法、UpdateとInsertを同時に！？
コメント