UPDATE文(SQLを基本から学ぶシリーズ)

 2021.04.08  株式会社システムインテグレータ

芸人ねづっちが登場する、IT系イベント 6/25(金)開催!code-rave

SQLにおけるUPDATE文は、基本的には「どのテーブルの何をどう更新するか」を記述したものです。さらに、UPDATE文にはとてもたくさんのバリエーションがあります!固定の値で更新するのはもちろんのこと、とても便利な書き方が可能なのです。

本記事ではUPDATE文の基本構文から始まり、文字列結合やテーブル結合を活用したUPDATE文、それらにまつわるよくあるエラーやアンチパターンまで解説します。

1 本記事に出てくるSQLは、MySQLを使って動作検証をしています。SQLの前には、そのSQLの仕様(何をどう更新したいのか)を書いてありますので、仕様とSQLを比べて見て理解を深めてください。

※2 記事中に正規化の考え方に反する箇所がいくつかありますが、本記事はSQLを解説することを主目的としているのでご容赦ください。

UPDATE文の基本

まずはUPDATE文の基本をおさらいしておきましょう。基本書式は以下のとおりです。

仕様:テーブルの全データの中から、条件に合ったものだけを更新する

UPDATE (表名) SET (カラム名1) = (値1) WHERE (条件);

カラムが複数の場合は、カンマで区切ります。

仕様:テーブルの全データの中から、条件に合ったものだけを更新する(複数カラム)

UPDATE (表名) SET (カラム名1) = (値1), (カラム名2) = (値2) WHERE (条件);

 

UPDATE対象が全レコード

ではまず、一番シンプルな「全レコードを更新する場合」を見てみましょう。

仕様:テーブル employeesnameというカラムを「名前」という値で更新する。

UPDATE employees SET name = '名前'

ここは基本中の基本なので迷うことはありませんね。ですが、全レコードを更新することは稀です。だいたい条件を指定して、それに合うレコードだけ更新します。

UPDATE対象を限定する(1) WHERE

次は、特定の条件に合うものだけを更新してみましょう。SELECT文と同様に、WHERE句を使います。

仕様:テーブル employeesのうち、genderMの社員について、titleというカラムにMr.と入れる。

UPDATE employees SET title = 'Mr.' WHERE gender = 'M'

 

UPDATE対象を限定する (2) IN

WHERE句は更新対象を絞るために記述するものです。ですが、「このデータとこのデータだけ!」と限定したい場合があります。例えば従業員No10006、1000710009のデータだけ更新したいけど、3レコードに共通した条件がない、といったケースです。

このような場合は、個別にSELECT WHERE emp_no=10006SELECT WHERE emp_no=10007... と複数文SQLを書いてもよいのですが、SELECT文同様にIN句が使えます。

仕様:テーブル employeesのうち、emp_no100061000710009のレコードのカラムtitleMs.と入れる。

UPDATE employees SET title = 'Ms.'
WHERE emp_no IN (10006, 10007, 10009)

文字列結合した内容でUPDATE

文字列結合したものでUPDATEすることもできます。

仕様:テーブル employeesにおいて、first_namelast_nameを連結してnameに更新する。

UPDATE employees SET name = CONCAT(first_name, last_name);

文字列の連結は言語ごとに「方言」があるので注意が必要です。例えばMySQLは文字列の連結にCONCATを使いますが、他に||演算子でも文字列連結が可能です。

ただしMySQL||を使う場合には、

SET sql_mode = 'PIPES_AS_CONCAT';

を前もって実行しておく必要があります。上記の内容はデータベースを再起動すると元に戻ってしまい、再び || が使えなくなります。よって永続的に有効にするにはmy.cnfに記述しておくなどの対応が必要です。

OraclePostgreSQLは設定なしで || が使えたり、AccessSQLServerでは + であったりとデータベースによって異なるので注意しましょう。

別テーブルの内容でUPDATE

別テーブルの内容を取得して、その値でUPDATEすることができます。例えば、従業員テーブルに上司氏名というカラムを追加して、そのカラムに上司氏名を入れたい、というケースです。

仕様:テーブル employeesに今回manager_nameというカラムを追加したので、上司氏名を更新する。ただし上司氏名はmanagerテーブルにある。

UPDATE employees SET employees.manager_name =
( SELECT manager.manager_name FROM manager
  WHERE employees.emp_no = manager.emp_no );

カッコの中は副問合せまたはサブクエリといいます。

ここで注意があります。ここの副問合せは必ず1行だけ結果を返す必要があります。2行以上の結果を返せば、以下のエラーが発生してしまうのです。

Error Code: 1242. Subquery returns more than 1 row
(副問合せが1行以上の結果を返しました)

UPDATE employees SET employees.manager_name =
のところは、先頭から1行づつ処理対象とします。その1行に対してカッコの中が2行以上の結果を返せば、どちらのmanager_nameを採用するのか分かりません。よって上記のエラーが発生します。

このような理由から、この場合の副問合せは1行の結果を返すようにWHERE句を記述する必要があるのです。

UPDATE文の応用

UPDATE文は、まだまだ他にも書き方があります。さらに便利な書き方を見ていきましょう。

テーブル結合した内容でUPDATE

SELECT文でよく見るJOIN句を使うこともできます。先ほど1-5で出てきたSQLを、同じ仕様のままJOINを使って書き直してみましょう。

UPDATE employees JOIN manager
ON employees.emp_no = manager.emp_no
SET employees.manager_name = manager.manager_name


SELECT
句と同じように、テーブル名が出てきたところでJOIN〜と書き続けていきます。それらが終わるとSET句が始まります。通常のUPDATE文よりテーブル部分の記述が大きくなったというところでしょうか。

上で出てきた「副問合せを使った場合」と比べてどちらがよいか、というのは特にありません。お好みの書き方を選んでください。

UPDATEしたい内容にCASEを使う

SET (カラム名) = 〜のところで、CASE文を使って条件により書き込み内容を変化させることができます。

仕様:テーブルemployeesにおいて、gender(性別)がMの場合はtitle(敬称)にMr.を入れる。genderFの場合はMs.を入れる。

UPDATE employees SET employees.title =
CASE employees.gender
WHEN 'M' THEN 'Mr.'
WHEN 'F' THEN 'Ms.'
END;

UPDATE文のアンチパターン

UPDATE文には便利な書き方があることを解説してきました。しかし覚えたことを使っていくうちに、いろいろ面倒なことが起こるはずです。

UPDATE文のアンチパターンを見ていきましょう。

UPDATE内容が複雑すぎる

これはUPDATE文に限ったことではありませんが、念のためお伝えしておきます。

Webシステムの開発で、入力した内容をデータベースに登録する場合を考えてみましょう。今回覚えたことを活用し、入力内容を条件に応じて変化させ、また誤入力にも対応するためにCASE文をたくさん書き・・・ということが重なると、SQLはどんどん複雑になります。
この状態で動作上の問題が発生したとき、SQLとアプリケーションのどちらに問題があるか、両方を調査しなければなりません。

プロジェクトによって考え方は変わりますが、入力値チェックや値の制御はアプリ、DBは純粋にデータの制御のみ、と機能をシンプルに分けたほうがあとあと管理しやすくなります。

主キーやINDEXをUPDATE

基本的にどんな項目でもUPDATEは可能です。ただし、主キーやINDEXUPDATEするのは慎重になりましょう。

例えば、employeesの主キー項目がemp_noだったとします。可能かどうかでいうと、可能です。

UPDATE employees SET emp_no = emp_no + 1;

ただし、emp_no1を足したとき、足した番号がすでに存在していればエラーになります。さらに外部結合などしていると、主キーの変更は他テーブルにも及びます。INDEXも同様です。

まとめ

記事に出てきたUPDATE文の基礎をもとに、より質の良いSQL文を書けるように学習を進めてみてください。

また、以下の無料ダウンロード資料も本記事と合わせてご活用くだされば幸いです。

sql_textbook_blog

 


RELATED POST関連記事


RECENT POST「SQL」の最新記事


この記事が気に入ったらいいねしよう!

om