Oracle SQL データの更新 :UPDATE文の基礎
Oracle SQL データの更新 :UPDATE文の基礎 |
|
|
|
|
| UPDATE文 | 備考 |
| ■UPDATE文の構文 | |
| ■クエリーの結果によるUPDATE | |
| ■クエリーの結果による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する場合に、実際の処理において上述の様なある一定値の更新を行うことは少なく、あるテーブルのクエリーの結果により他のテーブルを更新すると言う処理のほうが多いと思われます。
全ての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文における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行が更新されました。