Oracle SQL データの更新 :UPDATE文の基礎
- UPDATE文の基本的な使い方
- SET句の中にSELECTでの結果での更新
- EXISTS句によるUPDATE処理
- クエリーの結果に集計関数を使用
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
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 ****************************************
売上番号が６で明細番号が３の売上数量が元の値の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_売上明細の商品コードが１である明細の備考を"****"に設定しておきます。
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で数値１を返す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行が選択されました。
クエリーの結果によるUPDATE２
クエリーの結果が１個のみの場合ではなく２個以上の場合のほうが多いと思われます。
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行が更新されました。
クエリーの結果によるUPDATE３
クエリー結果の更新として、集計結果の値を更新する例を示します。 得意先マスタに売上の最終日を更新します。今回の例では、売上データしか無いので、売上を取り上げていますが一般的には 請求締め処理での最終請求締め日などにも応用できるかと思います。 先に、得意先マスタの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>
また、仮に請求締日が２０日の得意先を対象にすることで対象を絞ることができます。
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>
インラインビューを使ったUPDATE
更新可能なビュー（集合演算子、DISTINCT演算子、集計グループ関数などを使用していないビュー）であれば、UPDATE文の実行はできます。 UPDATEの次に更新可能なビューのビュー名を記述するところを、インラインビューにすれば特に前もってビューを作成する必要がありません。以下に、インラインビューを使った更新の例を示します。（尚、これは「クエリーの結果によるUPDATE２」で行った更新と同じ結果が得られます。）
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_商品の売上単価に別名をつけてあります。
インラインビューの中をうまく作ってやれば、応用が利くと思われます。
