SQL

SQLでテーブル内のレコードを更新する操作、UPDATEの文法について

knight_rose

開発エンジニア/システム開発歴19年/ボールド歴１年半

• 最終更新日:2019/6/12

SQLにおけるデータ操作言語 (DML)の1つで、テーブル内のデータ内容を変更するときに使う命令がUPDATE文です。

UPDATE文の基本に立ち返り、計算式、case式、複数テーブルの取り扱いについて、説明して行きましょう。

１．レコードを更新する

UPDATE文は１つのテーブル内の１レコード、または複数レコードのデータを変更する文です。全てのレコードを更新することも、条件に当てはまるレコードを選択して更新することもできます。

１-１．基本構文

UPDATE テーブル名
   SET 列名 = 変更内容, ...
 WHERE 条件式;

「テーブル名」は、データを変更するテーブル名を指定します。

「列名 = 変更内容」は、変更対象の列（「列名」）をどのように変更するか（「変更内容」）を指定します。「列名 = 変更内容」をカンマで区切って、複数指定することもできます。「列名」で指定されていない列は変更されません。

「条件式」はデータを変更するレコードを絞り込むための式です。指定の仕方はSELECT文と同様です。

１-２．テストデータ

SQLを説明するに当たって、理解しやすいように次の図のようなモデルケースを設定します。ここから先は、具体例や使用例などを説明する際に、このモデルケースの用語やデータを使って説明します。

商品

納品

納品明細

１-３．全ての行を更新する

「WHERE 条件式」をまったく指定しないと、すべてのレコードが変更対象となります。

次のようなUPDATE文を実行してみましょう。

UPDATE 商品
   SET 価格 = 100;

このUPDATE文を実行すると、“商品“表の“価格“列のすべての値は100に変更されます。実行後に検索をすると、“商品“表は次のようになります。

商品

１-４．条件に当てはまる行を更新する

「WHERE 条件式」の条件式に条件を指定すると、当てはまるレコードのデータが変更対象となります。

続けて、次のようなUPDATE文を実行してみましょう。

UPDATE 商品
   SET 価格 = 170
 WHERE 商品ID = 'S01';

このUPDATE文を実行すると、商品ID’S01’の“価格“列の値は170に変更されます。UPDATE文の実行後に“商品“表を検索をすると、次のようになります。

商品

商品ID = ‘S01’」に当てはまるレコードは１行目のレコードだけなので、“価格“列の値が更新されるのは、１行目のレコードだけです。

 複数の列を同時に更新することもできます。さらに続けて、次のようなUPDATE文を実行してみましょう。

UPDATE 商品
   SET 価格 = 250,
       商品名 = 'ダブルクリップ'
 WHERE 商品ID = 'S03';

このUPDATE文を実行すると、商品ID’S03’の“価格“列の値は250、”商品名“列の値は‘ダブルクリップ’に変更されます。UPDATE文の実行後に“商品“表を検索をすると、次のようになります。

商品

「商品ID = ‘S03’」に当てはまるレコードは３行目のレコードだけなので、“価格“列と“商品名“列の値が更新されるのは、３行目のレコードだけです。

「条件式」に当てはまるレコードが１つもない場合には、どのレコードは変更されません。さらに、次のようなUPDATE文を実行してみましょう。

UPDATE 商品
   SET 価格 = 900
 WHERE 商品ID = 'S99';

このUPDATE文を実行しても、「商品ID = ‘S09’」に当てはまるレコードがありませんので、すべてのレコードの値は実行前と同じです。UPDATE文の実行後に“商品“表を検索をすると、次のようになり、変更されていないことが確認できます。

商品

なお、このように当てはまるレコードが１つもない条件式を指定しても、エラーは発生しません。

１-５．計算した結果で更新する

「変更内容」には計算式を使うこともできます。１-４に続いて、次のようなUPDATE文を実行してみましょう。

UPDATE 商品
   SET 価格 = 価格 * 1.2
 WHERE 商品ID = 'S02';

このUPDATE文を実行すると、商品ID’S02’の“価格“列の値は変更前の“価格“列の値の1.2倍、即ち120に変更されます。UPDATE文の実行後に“商品“表を検索をすると、次のようになります。

商品

「商品ID = ‘S02’」に当てはまるレコードは２行目のレコードだけなので、“価格“列の値が更新されるのは、２行目のレコードだけです。

１-６．条件分岐(CASE式)の結果で更新する

「変更内容」には条件分岐(CASE式)を使うこともできます。次のようなUPDATE文を実行してみましょう。

UPDATE 納品明細
   SET 納品数 = CASE
                  WHEN 納品数 > 30 THEN 納品数 + 5
                  ELSE 納品数 + 3
                END
 WHERE 商品ID = 'S02';

このUPDATE文を実行すると、商品ID’S02’の“納品数“列の値は変更前の“納品数“列の値が30より大きい場合は変更前の“納品数“列の値＋５、それ以外の場合は変更前の“納品数“列の値＋３に変更されます。UPDATE文の実行後に“納品明細“表を検索をすると、次のようになります。

納品明細

「商品ID = ‘S02’」に当てはまるレコードは３行目と４行目のレコードで、“価格“列の値が更新されるのは、３行目と４行目のレコードだけです。３行目の変更前の“納品数“列の値は20なので、変更後は変更前の“納品数“列の値＋３、即ち23となります。４行目の変更前の“納品数“列の値は40なので、変更後は変更前の“納品数“列の値＋５、即ち45となります。

１-７．NULLで更新する

「変更内容」にNULLを指定することもできます。続けて、次のようなUPDATE文を実行してみましょう。

UPDATE 納品明細
   SET 納品数 = NULL
 WHERE 商品ID = 'S03';

このUPDATE文を実行すると、商品ID’S03’の“納品数“列の値はNULLに変更されます。UPDATE文の実行後に“納品明細“表を検索をすると、次のようになります。

納品明細

商品ID = ‘S03’に当てはまるレコードは５行目のレコードだけなので、“価格“列の値がNULLで更新されるのは、２行目のレコードだけです。

２．別のテーブルで条件を指定して更新する

「WHERE 条件式」の「条件式」に副問い合わせを使うことで別のテーブルを参照する条件を指定することもできます。１-２で最初にあげたテストデータに戻って、話を進めましょう。１－２のデータは次のようなものでした。

商品

納品

納品明細

例えば、次のようなUPDATE文を実行してみましょう。

UPDATE 商品
   SET 価格 = 価格 * 1.1
 WHERE 商品ID IN (
         SELECT 商品ID
           FROM 納品明細
          WHERE 納品数 < 30
       );

“納品明細”表の”納品数”列の値が30未満となるような商品IDと一致する”商品”表の“価格“列の値は変更前の“価格“列の値の1.1倍となります。

一つずつ順に説明していきましょう。

SELECT 商品ID
  FROM 納品明細
 WHERE 納品数 < 30

このSELECT文で取得される商品IDの一覧は‘S01’と‘S02’です。したがって、以下のUPDATE文と同じ意味になります。

UPDATE 商品
   SET 価格 = 価格 * 1.1
 WHERE 商品ID IN ('S01', 'S02');

このUPDATE文を実行すると、商品ID’S01’と’S02’の”商品”表の“価格“列の値は変更前の“価格“列の値の1.1倍、即ちそれぞれ165と88に変更されます。UPDATE文の実行後に“商品“表を検索をすると、次のようになります。

商品

「商品ID IN (‘S01’, ‘S02’)」に当てはまるレコードは１行目と２行目のレコードだけなので、“価格“列の値が更新されるのは、１行目と２行目のレコードとなります。

同じ更新処理を行うUPDATE文の書き方は他にもあります。上記UPDATE文では、IN句を使いましたが、EXISTS句を使って次のように書くこともできます。

UPDATE 商品
   SET 価格 = 価格 * 1.1
 WHERE EXISTS (
         SELECT 1
           FROM 納品明細
          WHERE 納品明細.納品数 < 30
            AND 納品明細.商品ID = 商品.商品ID
       );

こちらも一つずつ順に説明していきましょう。

更新対象の候補として“商品“表の１行目のレコードが取り出されたとします。商品ID’S01′です。次のSELECT文が実行されます。

SELECT 1
  FROM 納品明細
 WHERE 納品明細.納品数 < 30
   AND 納品明細.商品ID = 'S01'

“納品“表に“納品数“列の値が30未満且つ商品IDが‘S01’の行が１行目にあり、検索結果が存在するので、以下のEXISTS句の結果は真(TRUE)となります。

EXISTS (
  SELECT 1
    FROM 納品明細
   WHERE 納品明細.納品数 < 30
     AND 納品明細.商品ID = 商品.商品ID
)

よって、“商品“表の商品ID’S01′は更新対象となります。

更新対象の候補として“商品“表の２行目のレコードが取り出されたとします。商品ID’S02′です。次のSELECT文が実行されます。

SELECT 1
  FROM 納品明細
 WHERE 納品明細.納品数 < 30
   AND 納品明細.商品ID = 'S02'

“納品明細“表に“納品数“列の値が30未満且つ商品IDが‘S02’の行が３行目にあり、検索結果が存在するので、以下のEXISTS句の結果は真(TRUE)となります。

EXISTS (
  SELECT 1
    FROM 納品明細
   WHERE 納品明細.納品数 < 30
     AND 納品明細.商品ID = 商品.商品ID
)

よって、“商品“表の商品ID’S02′も更新対象となります。

更新対象の候補として“商品“表の３行目のレコードが取り出されたとします。商品ID’S03′です。次のSELECT文が実行されます。

SELECT 1
  FROM 納品明細
 WHERE 納品明細.納品数 < 30
   AND 納品明細.商品ID = 'S03'

“納品明細“表に“納品数“列の値が30未満且つ商品IDが‘S03’の行がなく、検索結果が存在しないので、以下のEXISTS句の結果は偽(FALSE)となります。

EXISTS (
  SELECT 1
    FROM 納品明細
   WHERE 納品明細.納品数 < 30
     AND 納品明細.商品ID = 商品.商品ID
)

よって、“商品“表の商品ID’S03′は更新対象となりません。

以上をまとめると、商品ID’S01’と’S02’が更新対象となり、商品ID’S01’と’S02’の”商品”表の“価格“列の値は変更前の“価格“列の値の1.1倍、即ちそれぞれ165と88に変更されます。

３．別のテーブルの値で更新する

これまで「列名 = 変更内容」の「変更内容」に定数や計算式などを指定するケースを見てきましたが、「変更内容」に相関副問い合わせを使うことで、別のテーブルの値で更新することができます。

再び、１－２で最初にあげたテストデータに戻って、話を進めましょう。１－２のデータは次のようなものでした。

商品

納品

納品明細

３-１．まずは基本構文通りに(ANSI/ISO)

例えば、次のようなUPDATE文を実行してみましょう。

UPDATE 納品明細
   SET 顧客ID = (
         SELECT 顧客ID
           FROM 納品
          WHERE 納品.納品ID = 納品明細.納品ID
       )
 WHERE EXISTS (
         SELECT 1
           FROM 納品
          WHERE 納品.納品ID = 納品明細.納品ID
       );

“納品”表の”納品ID”列の値に存在する”納品ID”を持つ”納品明細”表の“顧客ID”列の値を同じ“納品ID”列の値を持つ”納品”表の”顧客ID”列の値に変更されます。UPDATE文の実行後に“商品“表を検索をすると、次のようになります。

納品明細

ここでも一つずつ順に説明していきましょう。

更新対象の候補として“納品明細“表の１行目のレコードが取り出されたとします。納品ID’D01′です。次のSELECT文が実行されます。

SELECT 1
  FROM 納品
 WHERE 納品.納品ID = 'D01'

“納品“表に納品IDが‘D01’の行が１行目にあり、検索結果が存在するので、以下のEXISTS句の結果は真(TRUE)となります。

EXISTS (
  SELECT 1
    FROM 納品
   WHERE 納品.納品ID = 納品明細.納品ID
)

よって、“納品明細“表に納品ID’D01′は更新対象となります。続けて、「変更内容」を求めるため、次のSELECT文が実行されます。

SELECT 顧客ID
  FROM 納品
 WHERE 納品.納品ID = 'D01'

“納品”表の納品ID’D01′の“顧客ID”列の値‘C01’が取得され、「SET 顧客ID = ‘C01’」となります。１行目の”顧客ID”列の値は’C01’に変更されます。

 更新対象の候補として“納品明細“表の２行目のレコードが取り出されたとします。納品ID’D02′です。次のSELECT文が実行されます。

SELECT 1
  FROM 納品
 WHERE 納品.納品ID = 'D02'

“納品“表に納品IDが‘D02’の行が２行目にあり、検索結果が存在するので、以下のEXISTS句の結果は真(TRUE)となります。

EXISTS (
  SELECT 1
    FROM 納品
   WHERE 納品.納品ID = 納品明細.納品ID
)

よって、“納品明細“表に納品ID’D02′は更新対象となります。続けて、「変更内容」を求めるため、次のSELECT文が実行されます。

SELECT 顧客ID
  FROM 納品
 WHERE 納品.納品ID = 'D02'

“納品”表の納品ID’D02′の“顧客ID”列の値‘C22’が取得され、「SET 顧客ID = ‘C22’」となります。２行目の”顧客ID”列の値は’C22’に変更されます。

３行目以降も同様に「WHERE 条件式」が評価され、「条件式」の結果が真(TRUE)であれば、「SET 列名 = 変更内容」が実行されていきます。

これまではANSI/ISOのSQLの規格に基づいて説明してきました。特定のデータベース管理システムによらず適用できるケースが多いので、まずは基本構文通りに実行することをお勧めします。

しかし、データベース管理システム固有の構文を使うことで、パフォーマンスの問題などを解決できる場合があります。以下は４大^※1データベース管理システムについて、固有の構文を紹介しましょう。

３-２．Oracleの場合

Oracleではインライン・ビューで、次のように書くことができます。

UPDATE (
         SELECT 納品明細.顧客ID AS 旧顧客ID,
                納品.顧客ID AS 新顧客ID
           FROM 納品明細, 納品
          WHERE 納品.納品ID = 納品明細.納品ID
       )
   SET 旧顧客ID = 新顧客ID;

一つずつ順に説明していきましょう。

まず、“納品明細“表と“納品“表が「納品.納品ID = 納品明細.納品ID」と言う条件で内部結合されます。次のようなデータになります。

「納品明細.顧客ID」と「納品.顧客ID」が選択されて、列名にそれぞれ「旧顧客ID」、「新顧客ID」と言う別名が割り当てられます。

「SET 旧顧客ID = 新顧客ID」が適用されると、次のようになります。

これは、元の“納品明細“表と“納品“表に戻せば、次のようなデータとなります。

“納品明細“表を見ると３－１と同じ結果です。なお、「INNER JOIN」を使うと、次のようにも書くことができます。

UPDATE (
         SELECT 納品明細.顧客ID AS 旧顧客ID,
                納品.顧客ID AS 新顧客ID
           FROM 納品明細
          INNER JOIN 納品
             ON 納品.納品ID = 納品明細.納品ID
       )
   SET 旧顧客ID = 新顧客ID;

３-３．MySQLの場合

MySQLでは結合と組み合わせて、次のように書くことができます。

UPDATE 納品明細, 納品
   SET 納品明細.顧客ID = 納品.顧客ID
 WHERE 納品.納品ID = 納品明細.納品ID;

一つずつ順に説明していきましょう。

まず、“納品明細“表と“納品“表が「納品.納品ID = 納品明細.納品ID」と言う条件で内部結合されます。次のようなデータになります。

「SET 納品明細.顧客ID = 納品.顧客ID」が適用されると、次のようになります。

“納品明細“表を見ると３－１と同じ結果です。なお、「INNER JOIN」を使うと、次のようにも書くことができます。

UPDATE 納品明細
 INNER JOIN納品
    ON 納品.納品ID = 納品明細.納品ID
   SET 納品明細.顧客ID = 納品.顧客ID;

３-４．SQL Serverの場合

SQL Serverでも結合と組み合わせて、次のように書くことができます。

UPDATE 納品明細
   SET 納品明細.顧客ID = 納品.顧客ID
  FROM 納品明細, 納品
 WHERE 納品.納品ID = 納品明細.納品ID;

更新までの流れはMySQLと同様です。「INNER JOIN」を使うと、次のようにも書くことができます。

UPDATE 納品明細
   SET 納品明細.顧客ID = 納品.顧客ID
  FROM 納品明細
 INNER JOIN 納品
    ON 納品.納品ID = 納品明細.納品ID;

３-５．PostgreSQLの場合

PostgreSQLでも結合と組み合わせて、次のように書くことができます。

UPDATE 納品明細
   SET 納品明細.顧客ID = 納品.顧客ID
  FROM 納品
 WHERE 納品.納品ID = 納品明細.納品ID;

更新までの流れはMySQLと同様です。

PostgreSQLではSQL Serverと異なり、FROM句に更新対象のテーブルを指定しません。このような構文であるため、更新対象のテーブルと「INNER JOIN」を使って書くことはできません。

３-６．よく発生するエラー

３-１では、「列名 = 変更内容」の「変更内容」に副問い合わせを使いました。しかし、この副問い合わせの結果が２レコード以上あると、どのレコードを代入するか特定できないため、エラーになってしまいます。以下、このエラーについて詳しく見ていきましょう。

例えば、次のようなUPDATE文を実行してみましょう。

UPDATE 納品明細
   SET 顧客ID = (
         SELECT 顧客ID
           FROM 納品, 納品明細
          WHERE 納品.納品ID = 納品明細.納品ID
       )
 WHERE EXISTS (
         SELECT 1
           FROM 納品
          WHERE 納品.納品ID = 納品明細.納品ID
       );

このUPDATE文は３－１のUPDATE文とよく似ていますが、エラーとなります。「変更内容」を見ると、３-１では

(
  SELECT 顧客ID
    FROM 納品
   WHERE 納品.納品ID = 納品明細.納品ID
)

となっていますが、エラーとなる「変更内容」は以下のようになっています。

(
  SELECT 顧客ID
    FROM 納品, 納品明細
   WHERE 納品.納品ID = 納品明細.納品ID
)

一見すると同じようにみえますが、実は全く異なります。

３-１で「WHERE 納品.納品ID = 納品明細.納品ID」の“納品明細”表はUPDATE文で更新する表を指していますが、エラーとなるUPDATE文では副問い合わせのFROM句に指定された“納品明細”表を指しているのです。

 ３-１では納品ID’D01’を更新しようとする時の副問い合わせの結果は顧客ID’C01’だけです。

一方、エラーとなるUPDATE文では、納品ID’D01’を更新しようとする時でも、FROM句はすべての“納品明細”表を返しますので、副問い合わせの結果は顧客ID’C01′,’C22′,’C22′,’C03′,’C03’です。副問い合わせの結果は２レコード以上あることになり、エラーとなってしまいます。

今回の例では、「変更内容」の副問い合わせのFROM句から更新する表を外せば、エラーは発生しなくなります。

※１　DB-Engines Ranking（https://db-engines.com/en/ranking）の１～４位に基づく

４．さいごに

SQLのUPDATE文について、基本構文に従って徐々に複雑な事例について説明しました。ここで取り上げた事例で多くのケースがカバーできると思います。

データベース管理システムの種類は多数あり、それぞれ方言がありますが、基本構文で実現できる範囲はかなり広いので、まずは基本構文に基づいてアプローチすることをおすすめします。基本で何か課題があった場合に、そのデータベース管理システム固有のアプローチを検討するとよいと思います。

皆様の課題の解決にお役に立てれば幸いです。