MySQLのUPDATEの基本とデータベースに格納された値を効率よく変更する方法

MySQLのUPDATE文について、SQLを学び始めたばかりの方向けの基本的なことを解説し、さらに、こんな使い方もできる、といったUPDATE文の応用例を紹介します。

UPDATE文の基本的な使い方

SQLの基本コマンドと言えば、格納されたデータを抽出するSELECT、データベースにデータを登録するINSERT、そして、既に登録されたデータを変更するUPDATEの3つです。今回は、この3つの中のUPDATEについて解説しますが、まずは、UPDATEの基本について解説します。

データベースのデータを更新する処理

プログラムを学んだ方なら、配列の中のあるデータを修正する場合、それが配列のどこにあるかを指定しなければならないことは理解できるでしょう。これは、データベースを扱うSQLも同じです。そのデータがどこのレコードに含まれているかを指定し、変更したい列に値を指定すれば、そのデータが変更されます。

MySQLで、こうしたデータの変更する際に使うのがUPDATE文で、これはSQLの基本コマンドの一つです。そして、どこのデータを修正するかは、SELECT文などでもよく使われるWHERE句で指定します。なお、このWHEREを指定し忘れると、その列が全て同じ値に書き換わってしまうので注意してください。

UPDATEはWHERE句とセットで使う

次に、MySQLにおけるUPDATE文の文法を紹介します。

UPDATEに続いてテーブル名を、次のSETから列名と値を記述します。なお、列名と値を複数指定する場合はコンマで区切ってください。そして、WHERE句で、どの値を変えるかを指定するための条件を設定します。

なお、この条件でレコードが1つだけ特定できる場合は、そのレコードの指定した列の値を変更します。具体的には、ユニークな数字や記号が格納されたプライマルキーで指定するのが一般的です。また、条件を設定し、複数のレコードが特定された場合は、その複数のレコードで、その列の値が変更されます。ちょうど、SELECT文で抽出した複数のレコードの、該当する列を書き換えるのと同じです。

１件のみ変更するUPDATEの実行例

UPDATE table1 \
SET price=498 \
WHERE pid=1020190830;

複数を変更するUPDATEの実行例

UPDATE tabke2 \
SET userid=201234, groupid=200 \
WHERE ( userid=100234 AND charge=2008 );

もしWHERE句を忘れたら

では、もしWHEREを書き忘れてUPDATE文を実行した場合はどうなるでしょうか。この場合、全てのレコードの該当する列の値を書き換えます。そのため、たった一行の間違いで、巨大なデータが一瞬で壊れることもあります。データをリセットするために使うことはありますが、WHERE句の無いUPDATE文の実行は、十分に注意して実行してください。

UPDATE文の特殊な使い方

SQLにおけるUPDATE文の基本は先ほど解説したとおりですが、UPDATEの使い方はそれだけではありません。式や関数で指定したり、データを削除することも可能です。次から、そういったMySQLにおけるUPDATEの特殊な使い方について解説します。

計算結果を登録する場合

データベースに値を登録する場合、文字列や数字を指定するのが一般的ですが、MySQLでは計算式も指定できます。既存の数字の計算結果を格納したり、文字列なら一律に文字を追加するといった使い方が可能です。次に、そういった計算式を使った例を紹介します。

計算式を使ったUPDATE文の例

UPDATE table1 \
SET 価格=価格*1.1;

この例は、table1の全てのレコードに対して、価格の数値を、1.1倍にする例です。

文字列を修正して格納するUPDATE文の例

UPDATE table1 \
SET 規格=CONCAT(規格, '[非課税]') \
WHERE 税率=0;

この例は、table1のレコードのうち、税率が0に一致するレコードの規格に格納された文字列に対して、CONCAT関数を利用して'[非課税]’という文字を追加して格納します。

なお、今回紹介したCONCAT()関数は文字列同志を結合する関数ですが、文字列を切り出す関数などもあり、もちろん、それらの関数をUPDATE文で利用できます。どういった関数が使えるかは、MySQLの公式サイトのマニュアルなどを参照してください。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5 文字列関数

登録済のデータを削除する場合

MySQLで実行するSQLには、データベースやテーブルを削除するコマンドはありますが、既に格納されているデータを削除するコマンドはありません。なお、プログラムで変数を初期化する場合、よくnullを代入しますがSQLでもこの方法が使えます。

この場合、プログラムによっては、「a = “”」で変数を初期化するケースもありますが、SQLでは、nullと「””」は別のデータとして扱われます。もし、SQLでCASEのような条件分岐を使う場合は、nullと「””」の違いで結果が違ってしまうこともあるので、データを初期化する場合は、必ずnullを使いましょう。

データを初期化するSQLの例

UPDATE table1 \
SET 価格=null \
WHERE pid=1020190830;

上の例では、pidで指定したレコードの価格にnullを入力してデータを初期化しています。

WHEREで条件を指定する場合の注意

MySQLに限らず、SQLのUPDATE文を作る場合、WHERE句でどうやってレコードを特定するかがポイントです。次からUPDATE文におけるWHERE句の作り方や注意点について解説します。

基本は主キーによる抽出

テーブルを作成する際、通常最初の列に主キー(PRIMARY KEY)を設定するのをご存じでしょうか。主キーには必ずユニークなデータが格納されることから、たくさんあるレコードの中から、1つのレコードを効率よく抽出することができます。

そのため、UPDATE文で1つのレコードの特定の列のデータを修正する場合は、WHERE句の抽出条件に主キーを指定します。

ポテパンダの一言メモ

MySQLでテーブルのある列に主キーを設定すると、その列には重複したデータが入力できなくなります。さらに、nullも格納できません。そのためSQLでは、1つのレコードを特定するために、WHERE句でよく使われます。

副次問い合わせを使う

SQLには、SELECT文で抽出したデータ、または、リストを使ってSELECT文などで利用する副次問い合わせ(サブクエリ)という機能があります。MySQLのUPDATE文でも利用できますが、制限があるので注意が必要です。

例えば、MySQLのバージョン5では、副次問い合わせで参照しているテーブルのデータを、UPDATE文で書き換えできません。Oracleなどの他のデータベースからSQLを移植する場合は、注意が必要です。なお、同じテーブルでなければUPDATEで書き換えできるので、JOIN句で一時的のテーブルを作れば書き換えは可能できます。

ただし、互換性のあるmariadbの新しいバージョンではエラーにはなりません。そのため、別のサーバーでMySQLの副次問い合わせが使えたからといって、同じSQLが全てのサーバーのMySQLで使える訳ではありません。

UPDATEによるデータ更新は個別でも一括でも可能

データベースに既に格納されたデータを修正するコマンドのUPDATEは、SQLでもよく使われる重要なコマンドの一つです。そして、その基本は、WHERE句で変更するレコードを特定し、その特定の列のデータのみを修正します。

しかし、UPDATEの使い方はそれだけではありません。今回ご紹介したように、MySQLでUPDATEを使うなら、計算式を指定できたり、同時に複数修正する方法もあるなど、いろいろな修正が可能です。必要に応じてUPDATEを使ったSQLを工夫してみてください。