Oracle SQL データの更新 :UPDATE文の基礎

RNK's HPのトップへ
UPDATE文 備考
■UPDATE文の構文
■クエリーの結果によるUPDATE
■クエリーの結果によるUPDATE2
■クエリーの結果によるUPDATE3 クエリーの結果に集計関数を使用
■インラインビューを使ったUPDATE4

       

■UPDATE文の構文

UPDATE <表名> 
    SET   <列名1> = <値の式1> 
        [,<列名2> = <値の式2> ]
        [,<列名3> = <値の式3> ] ...
  [ WHERE <検索条件> ]

UPDATE文では、WHERE句の検索条件に合致する行に対して、指定された列に指定された値を
設定します。WHERE句は必ずしも必要ではなく、その場合には指定された表の全ての行が更新
対象となります。

簡単なUPDATEの実行の例を示します。

SQL> SELECT * FROM TT_売上明細 WHERE 売上番号 = 6;

  売上番号   明細番号 商品コード   売上数量 備考
---------- ---------- ---------- ---------- ----------------------------------------
         6          1          8          1
         6          2          9          1
         6          3         10          1

SQL> UPDATE TT_売上明細 
  2   SET 商品コード = 10
  3      ,売上数量   = 2
  4   WHERE 売上番号 = 6
  5   AND   明細番号 = 3;

1行が更新されました。

SQL> SELECT * FROM TT_売上明細 WHERE 売上番号 = 6;

  売上番号   明細番号 商品コード   売上数量 備考
---------- ---------- ---------- ---------- ----------------------------------------
         6          1          8          1
         6          2          9          1
         6          3         10          2

WHERE句を指定しない場合の例を以下に示します。

SQL> SELECT * FROM TT_売上明細;

  売上番号   明細番号 商品コード   売上数量 備考
---------- ---------- ---------- ---------- ----------------------------------------
         1          1          1          2
         1          2          4          1
         2          1          2          1
         2          2          5          2
         2          3         11          1
         3          1          3          3
         4          1          6          1
         4          2          7          2
         5          1          8          3
         5          2          9          2
         6          1          8          1
         6          2          9          1
         6          3         10          2

13行が選択されました。

SQL> UPDATE TT_売上明細
  2   SET 備考 = '****************************************';

13行が更新されました。

SQL> SELECT * FROM TT_売上明細;

  売上番号   明細番号 商品コード   売上数量 備考
---------- ---------- ---------- ---------- ----------------------------------------
         1          1          1          2 ****************************************
         1          2          4          1 ****************************************
         2          1          2          1 ****************************************
         2          2          5          2 ****************************************
         2          3         11          1 ****************************************
         3          1          3          3 ****************************************
         4          1          6          1 ****************************************
         4          2          7          2 ****************************************
         5          1          8          3 ****************************************
         5          2          9          2 ****************************************
         6          1          8          1 ****************************************
         6          2          9          1 ****************************************
         6          3         10          2 ****************************************

13行が選択されました。

備考の列が全て"*"で埋められた様子がわかると思います。

構文説明でSET句の右辺が<値の式>と書いているのは、ここに式として判定できるものならば何を指定
しても良いということです。但し、左辺側の列のデータ型と同じものでなければなりません。
以下に<値の式>が単純な値のみではない場合の例を示します。

SQL> SELECT * FROM TT_売上明細 WHERE 売上番号 = 6;

  売上番号   明細番号 商品コード   売上数量 備考
---------- ---------- ---------- ---------- ----------------------------------------
         6          1          8          1 ****************************************
         6          2          9          1 ****************************************
         6          3         10          2 ****************************************

SQL> UPDATE TT_売上明細 
  2   SET 売上数量 = 売上数量 * 100
  3   WHERE 売上番号 = 6
  4   AND   明細番号 = 3;

1行が更新されました。

SQL> SELECT * FROM TT_売上明細 WHERE 売上番号 = 6;

  売上番号   明細番号 商品コード   売上数量 備考
---------- ---------- ---------- ---------- ----------------------------------------
         6          1          8          1 ****************************************
         6          2          9          1 ****************************************
         6          3         10        200 ****************************************

売上番号が6で明細番号が3の売上数量が元の値の100倍されている様子がわかると思います。
このことはデータ変更が柔軟に行えることを示唆していると思います。但し、計算結果のデータの桁数がデータ型で宣言されている桁数を超える場合はエラーが発生し、データの更新に失敗します。

■クエリーの結果によるUPDATE

データをUPDATEする場合に、実際の処理において上述の様なある一定値の更新を行うことは少なく、あるテーブルのクエリーの結果により他のテーブルを更新すると言う処理のほうが多いと思われます。
 
 全てのTT_売上明細の備考に、商品コードに対応する商品名を設定する場合を考えます。
以下のSQLでは2行目でTT_売上明細に対する変更のための副クエリーが含まれます。この副クエリーはTM_商品から商品名を選択するSELECT文で、4行目でTM_商品の商品コードとTT_売上明細の商品コードを結合しています。

SQL> UPDATE TT_売上明細 TT
  2   SET 備考 = (
  3    SELECT 商品名 FROM TM_商品 TM
  4    WHERE TM.商品コード = TT.商品コード
  5   );

17行が更新されました。

SQL> SELECT * FROM TT_売上明細;

  売上番号   明細番号 商品コード   売上数量 備考
---------- ---------- ---------- ---------- ----------------------------------------
         1          1          1          2 PC-9001
         1          2          4          1 NOTE-1010
         2          1          2          1 PC-9002
         2          2          5          2 NOTE-1020
         2          3         11          1 HUB-A001
         3          1          3          3 PC-9003
         4          1          6          1 NOTE-1030
         4          2          7          2 PRT-3001
         5          1          8          3 PRT-4001
         5          2          9          2 CRT-1001
         6          1          8          1 PRT-4001
         6          2          9          1 CRT-1001
         6          3         10          2 CRT-2001
         8          1          8          3 PRT-4001
         8          2          9          1 CRT-1001
         9          1          7          2 PRT-3001
         9          2          9          1 CRT-1001

17行が選択されました。

上の結果を見れば備考に商品名が設定されたことがわかります。但し、このUPDATE文は問題があります。TT_売上明細の商品コードがTM_商品に存在しない場合に、副クエリーの返す商品名がNULLを返すため、当然備考にはNULLが反映されてしまいます。
 このことを解消する為のテストとして、以下の処理を前もって行います。TM_商品の商品コードが1のものを100に変更し、かつTT_売上明細の商品コードが1である明細の備考を"****"に設定しておきます。

SQL> rollback;

ロールバックが完了しました。

SQL> UPDATE TM_商品
  2  SET 商品コード = 100
  3  WHERE 商品コード = 1;

1行が更新されました。

SQL> UPDATE TT_売上明細
  2  SET 備考 = '*****'
  3  WHERE 売上番号 = 1
  4  AND   明細番号 = 1;

1行が更新されました。

SQL> commit;

コミットが完了しました。

TT_売上明細の商品コードがTM_商品に存在する場合のみに限って、備考に商品名を設定するUPDATE文を以下に示します。
6行目以降のWHERE句でTM_商品とTT_売上明細の商品コードで結合し、結合が存在すればSELECTで数値1を返すSELECT文の結果をEXISTSで条件付けることでTT_売上明細の処理される行が決定されます。

SQL> UPDATE TT_売上明細 TT
  2   SET 備考 = (
  3    SELECT 商品名 FROM TM_商品 TM
  4    WHERE TM.商品コード = TT.商品コード
  5   )
  6  WHERE EXISTS (
  7   SELECT 1
  8   FROM TM_商品 TX
  9   WHERE TX.商品コード = TT.商品コード
 10  );

16行が更新されました。

SQL> SELECT * FROM TT_売上明細;

  売上番号   明細番号 商品コード   売上数量 備考
---------- ---------- ---------- ---------- ----------------------------------------
         1          1          1          2 *****
         1          2          4          1 NOTE-1010
         2          1          2          1 PC-9002
         2          2          5          2 NOTE-1020
         2          3         11          1 HUB-A001
         3          1          3          3 PC-9003
         4          1          6          1 NOTE-1030
         4          2          7          2 PRT-3001
         5          1          8          3 PRT-4001
         5          2          9          2 CRT-1001
         6          1          8          1 PRT-4001
         6          2          9          1 CRT-1001
         6          3         10          2 CRT-2001
         8          1          8          3 PRT-4001
         8          2          9          1 CRT-1001
         9          1          7          2 PRT-3001
         9          2          9          1 CRT-1001

17行が選択されました。

■クエリーの結果によるUPDATE2

クエリーの結果が1個のみの場合ではなく2個以上の場合のほうが多いと思われます。
UPDATE文におけるSET句では複数の更新を、副クエリーを用いて行うことが出来ます。
まづ、ALTER文によりTT_売上明細に、TM_商品の売上単価の項目を持つ様に変更します。
その後のUPDATE文を見れば、SETの後に括弧"()"で更新される項目名を列記し、その後でその項目に対応するデータを返す副クエリー文を"="で結びます。副クエリーのSELECT句の中のデータの並びはSET句でのデータの並びに合わせる必要があります。

SQL> ALTER TABLE TT_売上明細
  2   ADD 売上単価 NUMBER(9);


SQL> UPDATE TT_売上明細 TT
  2   SET (備考 , 売上単価) = (
  3    SELECT 商品名 , 売上単価 FROM TM_商品 TM
  4    WHERE TM.商品コード = TT.商品コード
  5   )
  6  WHERE EXISTS (
  7   SELECT 1
  8   FROM TM_商品 TX
  9   WHERE TX.商品コード = TT.商品コード
 10  );

16行が更新されました。

SQL> SELECT * FROM TT_売上明細;

  売上番号   明細番号 商品コード   売上数量 備考                                       売上単価
---------- ---------- ---------- ---------- ---------------------------------------- ----------
         1          1          1          2 *****
         1          2          4          1 NOTE-1010                                    188000
         2          1          2          1 PC-9002                                      120000
         2          2          5          2 NOTE-1020                                    200000
         2          3         11          1 HUB-A001                                      40000
         3          1          3          3 PC-9003                                      190000
         4          1          6          1 NOTE-1030                                    220000
         4          2          7          2 PRT-3001                                      88000
         5          1          8          3 PRT-4001                                     180000
         5          2          9          2 CRT-1001                                      78000
         6          1          8          1 PRT-4001                                     180000
         6          2          9          1 CRT-1001                                      78000
         6          3         10          2 CRT-2001                                      98000
         8          1          8          3 PRT-4001                                     180000
         8          2          9          1 CRT-1001                                      78000
         9          1          7          2 PRT-3001                                      88000
         9          2          9          1 CRT-1001                                      78000

17行が選択されました。

UPDATE...SET()の形式は実際の値を直接設定する以下の様な方法は不可能です。
但し、どうしても以下のようなことがしたい場合は、"="以降の中を無理やりSELECT文で値を返す様にしてやれば出来ます。

SQL> UPDATE TT_売上明細
  2  SET (備考,売上単価) = ('*****',NULL)
  3  WHERE 売上番号 = 1
  4  AND   明細番号 = 1;
SET (備考,売上単価) = ('*****',NULL)
                      *
2行でエラーが発生しました。
ORA-01767: UPDATE ... SET式は副問合せである必要があります

SQL> UPDATE TT_売上明細
  2  SET (備考,売上単価) = (SELECT '*****',NULL FROM DUAL)
  3  WHERE 売上番号 = 1
  4  AND   明細番号 = 1;

1行が更新されました。

■クエリーの結果によるUPDATE3

クエリー結果の更新として、集計結果の値を更新する例を示します。 得意先マスタに売上の最終日を更新します。今回の例では、売上データしか無いので、売上を取り上げていますが一般的には 請求締め処理での最終請求締め日などにも応用できるかと思います。 先に、得意先マスタのTM_得意先に最終売上日のカラムを追加します。
SQL> ALTER TABLE TM_得意先
  2  ADD 最終売上日 DATE;

表が変更されました。

SQL> SELECT 得意先コード,最終売上日 FROM TM_得意先;

得意先コード 最終売上
------------ --------
           1
           2
           3
           4
           5

SQL>
そこで以下のSQLを実行します。このSQLは更新されるTM_得意先の得意先コードと、TT_売上の得意先コードを紐付けし 該当する得意先の売上日の最大値を最終売上日に更新します。
SQL> UPDATE TM_得意先 TM
  2   SET 最終売上日 = (
  3     SELECT MAX(TT.売上日) FROM TT_売上 TT
  4     WHERE TT.得意先コード = TM.得意先コード
  5     GROUP BY TM.得意先コード
  6   );

5行が更新されました。

SQL> SELECT 得意先コード,最終売上日 FROM TM_得意先;

得意先コード 最終売上
------------ --------
           1 02-11-15
           2 02-11-03
           3 02-11-13
           4
           5 02-11-15

SQL>
これには少し問題がありまして、TM_得意先にのみ得意先コードが存在する場合、(TT_売上の方に得意先のコードが無い場合) 最終売上日にはNULLが更新されてしまいます。 NULLに更新されるのが問題であれば、以下の様な方法もあります。副クエリーで取得される売上日がNULLの場合、得意先マスタの 最終売上日を更新することで、更新処理は行われますが見かけ上値が変化しません。
SQL> UPDATE TM_得意先 TM
  2   SET 最終売上日 = (
  3     NVL(
  4      (
  5       SELECT MAX(TT.売上日) FROM TT_売上 TT
  6       WHERE TT.得意先コード(+) = TM.得意先コード
  7       GROUP BY TM.得意先コード
  8      ),
  9      TM.最終売上日
 10    )
 11   );

5行が更新されました。

SQL> SELECT 得意先コード,最終売上日 FROM TM_得意先;

得意先コード 最終売上
------------ --------
           1 02-11-15
           2 02-11-03
           3 02-11-13
           4
           5 02-11-15

SQL>
また、仮に請求締日が20日の得意先を対象にすることで対象を絞ることができます。
SQL> UPDATE TM_得意先 TM
  2   SET 最終売上日 = (
  3     SELECT MAX(TT.売上日) FROM TT_売上 TT
  4     WHERE TT.得意先コード(+) = TM.得意先コード
  5     GROUP BY TM.得意先コード
  6   )
  7   WHERE TM.請求締日 = 20;

2行が更新されました。

SQL>

■インラインビューを使ったUPDATE4

更新可能なビュー(集合演算子、DISTINCT演算子、集計グループ関数などを使用していないビュー)であれば、UPDATE文の実行はできます。 UPDATEの次に更新可能なビューのビュー名を記述するところを、インラインビューにすれば特に前もってビューを作成する必要がありません。
以下に、インラインビューを使った更新の例を示します。(尚、これは「クエリーの結果によるUPDATE2」で行った更新と同じ結果が得られます。)
SQL> UPDATE
  2   (
  3    SELECT TT.備考, TT.売上単価 , TM.商品名 , TM.売上単価 AS 商品単価
  4    FROM TT_売上明細 TT, TM_商品 TM
  5    WHERE TT.商品コード = TM.商品コード
  6   )
  7  SET
  8   備考 = 商品名
  9  ,売上単価 = 商品単価;

14行が更新されました。
インラインビューのカラム名でTT_売上明細とTM_商品で同じ売上単価のカラムが存在するので、TM_商品の売上単価に別名をつけてあります。
インラインビューの中をうまく作ってやれば、応用が利くと思われます。

RNK's HPのトップへ