SQLの全てがっここにある!

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

knight_rose

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

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

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


1.レコードを更新する

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

1-1.基本構文

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

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

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

1-2.テストデータ

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

モデルケース

商品

商品ID商品名価格
S01ボールペン150
S02消しゴム80
S03クリップ200

納品

納品ID顧客ID
D01C01
D02C22
D03C03

納品明細

納品ID明細番号商品ID顧客ID納品数
D011S01C0110
D021S01C0230
D022S02C0220
D031S02C0340
D032S03C0360

1-3.全ての行を更新する

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

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

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

商品

商品ID商品名価格
S01ボールペン100
S02消しゴム100
S03クリップ100

1-4.条件に当てはまる行を更新する

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

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

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

商品

商品ID商品名価格
S01ボールペン170
S02消しゴム100
S03クリップ100

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

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

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

商品

商品ID商品名価格
S01ボールペン170
S02消しゴム100
S03ダブルクリップ250

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

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

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

商品

商品ID商品名価格
S01ボールペン170
S02消しゴム100
S03ダブルクリップ250

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

1-5.計算した結果で更新する

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

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

商品

商品ID商品名価格
S01ボールペン170
S02消しゴム120
S03ダブルクリップ250

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

1-6.条件分岐(CASE)の結果で更新する

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

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

納品明細

納品ID明細番号商品ID顧客ID納品数
D011S01C0110
D021S01C0230
D022S02C0223
D031S02C0345
D032S03C0360

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

1-7.NULLで更新する

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

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

納品明細

納品ID明細番号商品ID顧客ID納品数
D011S01C0110
D021S01C0230
D022S02C0223
D031S02C0345
D032S03C03NULL

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


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

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

商品

商品ID商品名価格
S01ボールペン150
S02消しゴム80
S03クリップ200

納品

納品ID顧客ID
D01C01
D02C22
D03C03

納品明細

納品ID明細番号商品ID顧客ID納品数
D011S01C0110
D021S01C0230
D022S02C0220
D031S02C0340
D032S03C0360

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

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

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

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

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

商品

商品ID商品名価格
S01ボールペン165
S02消しゴム88
S03ダブルクリップ200

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

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

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

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

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

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

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

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

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

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

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

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

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


3.別のテーブルの値で更新する

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

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

商品

商品ID商品名価格
S01ボールペン150
S02消しゴム80
S03クリップ200

納品

納品ID顧客ID
D01C01
D02C22
D03C03

納品明細

納品ID明細番号商品ID顧客ID納品数
D011S01C0110
D021S01C0230
D022S02C0220
D031S02C0340
D032S03C0360

3-1.まずは基本構文通りに(ANSI/ISO)

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

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

納品明細

納品ID明細番号商品ID顧客ID納品数
D011S01C0110
D021S01C0230
D022S02C0220
D031S02C0340
D032S03C0360

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

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

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

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

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

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

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

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

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

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

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

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

3-2.Oracleの場合

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

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

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

納品明細納品
納品ID明細番号商品ID顧客ID納品数納品ID顧客ID
D011S01C0110D01C01
D021S01C0230D02C22
D022S02C0220D02C22
D031S02C0340D03C03
D032S03C0360D03C03

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

旧顧客ID新顧客ID
C01C01
C02C22
C02C22
C03C03
C03C03

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

旧顧客ID新顧客ID
C01C01
C22C22
C22C22
C03C03
C03C03

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

納品明細納品
納品ID明細番号商品ID顧客ID納品数納品ID顧客ID
D011S01C0110D01C01
D021S01C2230D02C22
D022S02C2220D02C22
D031S02C0340D03C03
D032S03C0360D03C03

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

3-3.MySQLの場合

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

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

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

納品明細納品
納品ID明細番号商品ID顧客ID納品数納品ID顧客ID
D011S01C0110D01C01
D021S01C0230D02C02
D022S02C0220D02C02
D031S02C0340D03C03
D032S03C0360D03C03

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

納品明細納品
納品ID明細番号商品ID顧客ID納品数納品ID顧客ID
D011S01C0110D01C01
D021S01C0230D02C02
D022S02C0220D02C02
D031S02C0340D03C03
D032S03C0360D03C03

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

3-4.SQL Serverの場合

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

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

3-5.PostgreSQLの場合

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

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

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

3-6.よく発生するエラー

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

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

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

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

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

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

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

納品明細納品
納品ID明細番号商品ID顧客ID納品数納品ID顧客ID
D011S01C0110D01C01

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

納品明細納品
納品ID明細番号商品ID顧客ID納品数納品ID顧客ID
D011S01C0110D01C01
D021S01C2230D02C02
D022S02C2220D02C02
D031S02C0340D03C03
D032S03C0360D03C03

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

※1 DB-Engines Rankinghttps://db-engines.com/en/ranking)の1~4位に基づく


4.さいごに

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

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

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

私たちは、全てのエンジニアに市場価値を高め自身の望む理想のキャリアを歩んでいただきたいと考えています。もし、今あなたが転職を検討しているのであればこちらの記事をご一読ください。理想のキャリアを実現するためのヒントが見つかるはずです。

『技術力』と『人間力』を高め市場価値の高いエンジニアを目指しませんか?

私たちは「技術力」だけでなく「人間力」の向上をもって遙かに高い水準の成果を出し、関わる全ての人々に感動を与え続ける集団でありたいと考えています。

高い水準で仕事を進めていただくためにも、弊社では次のような環境を用意しています。

  • 定年までIT業界で働くためのスキル(技術力、人間力)が身につく支援
  • 「給与が上がらない」を解消する6ヶ月に1度の明確な人事評価制度
  • 平均残業時間17時間!毎週の稼動確認を徹底しているから実現できる働きやすい環境

現在、株式会社ボールドでは「キャリア採用」のエントリーを受付中です。

まずは以下のボタンより弊社の紹介をご覧いただき、あなたの望むキャリアビジョンをエントリーフォームより詳しくお聞かせください。

コメント