【Oracle】別テーブルの値を用いて更新する方法

こんにちは。エンジニアのわっくんです。

ここ最近、SQLを扱う機会が多くなりました。いろいろと調べつつ、その都度学びながら過ごしています。
さて、今回は「別テーブルの値を用いて更新する方法」というテーマについて、ご説明します。例えば、テーブルAのカラムの値を更新するときに、「Bテーブルのカラムの値を用いて更新したい」ということがあると思います。そんなときはどのようにすれば良いでしょう?
普段は「UPDATE HOGE SET COL_A= ‘1’」みたいな感じで記述しているのに、どうしよう…??と考える人が多いと思います。
というわけで、別テーブルの値を用いて更新する方法を紹介したいと思います。

1. 別テーブルの値を用いて更新する
Oracleで特定のテーブルの値に対して、別テーブルの値を用いて更新する場合、以下の様に記述します。

UPDATE TAB_HOGE_1 T1
SET
T1. COL_A =
(SELECT
T2.COL_X
 FROM
  TAB_ HOGE_2 T2
 WHERE T2. KEY_COL = T1. KEY_COL);

上記の場合、TAB_HOGE_1テーブルのCOL_Aカラムに、TAB_ HOGE_2テーブルのCOL_Xカラムの値をセットするという意味になります。その際に、サブクエリの条件として、TAB_HOGE_1のKEY_COLとTAB_ HOGE_2のKEY_COLを結合しています。

尚、複数カラムを更新したい場合は、次のように記述します。

UPDATE TAB_HOGE_1 T1
SET
(T1. COL_A 
,T1. COL_B)
=
(SELECT
T2.COL_X
,T2.COL_Y
 FROM
  TAB_ HOGE_2 T2
 WHERE T2. KEY_COL = T1. KEY_COL);

それでは、実際に試してみましょう。今回、Oracle Live SQLで試してみます。

例として、「果物テーブル」と「果物色マスタ」を用意し、果物テーブルを更新してみます。

image1
用意した「果物テーブル」のFRUIT_COLOR_NAMEカラムには値が入っていませんので、「果物色マスタ」を参照して、「果物テーブル」の果物の色を設定します。「果物テーブル」のFRUIT_COLOR_CDカラムと「果物色マスタ」のFRUIT_COLOR_CDは紐づいており、「果物テーブル」の色を設定できそうです。

それでは、「果物テーブル」と「果物色マスタ」のFRUIT_COLOR_CDで結合して、「果物テーブル」に果物の色を設定してみます。

UPDATE FRUIT T01 
SET 
 ( 
  T01.FRUIT_COLOR_NAME 
 ) = 
 ( 
  SELECT 
   T02.FRUIT_COLOR_NAME 
  FROM 
   FRUIT_COLOR T02 
  WHERE T02.FRUIT_COLOR_CD = T01.FRUIT_COLOR_CD);

上記SQLを実行して、テーブルを確認します。
SQL実行!

image2
テーブル確認!

image3
「果物テーブル」のFRUIT_COLOR_NAMEが更新され、果物の色が設定されましたね。
ちなみに、FRUIT_COLOR_CDの値005,006,007は「果物色マスタ」に存在しないので、更新されません。

2.別テーブルに存在するレコードだけを更新
さて、先述したUPDATEのSQL文ですが、SQLの実行結果を見てみると、果物テーブルのすべてのレコードがUPDATEされています。

image4
image5
もも、20世紀ナシ、メロンは更新しなくていいのに…

「果物テーブル」のFRUIT_COLOR_CDカラムに存在する005, 006, 007は「果物色マスタ」には存在しませんが、「果物テーブル」のすべてのレコードを更新しています(結果的に、FRUIT_COLOR_NAMEはNULLになっています)。それもそのはずで、UPDATE文に条件を指定してないからです。
今回の例ではレコード数が少ないので、パフォーマンスには問題ありませんが、何万件、何十万件といった大量データを更新するとなると、SQLの実行に時間がかかってしまいます。そのため、「果物色マスタ」に存在する、という条件を指定する必要があります。

UPDATE FRUIT T01 
SET 
 ( 
  T01.FRUIT_COLOR_NAME 
 ) = 
 (SELECT 
   T02.FRUIT_COLOR_NAME 
  FROM 
   FRUIT_COLOR T02 
  WHERE T02.FRUIT_COLOR_CD = T01.FRUIT_COLOR_CD)
WHERE EXISTS (SELECT 1  
              FROM FRUIT_COLOR T02 
              WHERE T02.FRUIT_COLOR_CD = T01.FRUIT_COLOR_CD);

UPDATE文の条件にEXISTSを加えます。EXISTS条件の内容は、サブクエリで記述した条件と同じにすれば、別テーブルに存在するデータのみ更新します。
実際に試してみましょう。7レコード更新されていればOKです。

image6
ちゃんと「果物色マスタ」と紐づく「果物テーブル」の7レコードだけが更新されましたね。更新結果も同じです。

image7

まとめ

今回は、別テーブルの値を用いて特定のテーブルを更新する方法でした。構文自体はそんなに難しくないので便利ですが、EXISTS条件を指定しないと更新対象のテーブルにおいて、すべてのレコードが更新されてしまいますので、大量データを更新する時には注意が必要です。

なお、本記事で使用したOracle Live SQLについては、過去ブログにいくつか紹介していますので、そちらも合わせてご覧下さい。

株式会社ライズウィル

株式会社ライズウィル

〒103-0013
東京都中央区日本橋人形町2-15-1
フジタ人形町ビル7F
TEL : 03-4590-3200
FAX : 03-4590-3201
E-Mail : info@risewill.co.jp
URL : https://www.risewill.co.jp

海上コンテナ輸送管理システム「海コン魂!」
海コン魂!
フルカスタマイズ可能な求人サイトパッケージ
jobtter