UPDATE
前提条件
表の値を更新する場合は、表が自分のスキーマ内にある必要があります。自分のスキーマ内にない場合は、その表に対するUPDATE
オブジェクト権限が必要です。
ビューの実表の値を更新する場合は、次の条件を満たす必要があります。
-
そのビューに対する
UPDATE
オブジェクト権限を持っている。 -
そのビューが含まれているスキーマの所有者が、実表に対する
UPDATE
オブジェクト権限を持っている。
UPDATE
ANY
TABLE
システム権限を持っている場合は、任意の表またはビューの実表の値を更新できます。
リモート・データベースのオブジェクトの値を更新するには、オブジェクトのREAD
またはSELECT
オブジェクト権限も必要です。
SQL92_SECURITY
初期化パラメータがTRUE
に設定され、UPDATE
操作がwhere_clause
の列などの表の列を参照する場合、更新するオブジェクトのSELECT
オブジェクト権限も必要です。
構文
update::=
(DML_table_expression_clause::=、update_set_clause::=、where_clause::=、returning_clause::=、error_logging_clause::=、from_using_clause::=)
DML_table_expression_clause::=を参照
(partition_extension_clause::=、subquery::=(SELECT
の一部)、subquery_restriction_clause::=、table_collection_expression::=)
partition_extension_clause::=
subquery_restriction_clause::=
table_collection_expression::=
update_set_clause::=
from_using_clause::=
where_clause::=
order_by_clause::=
order_by_clause::=を参照してください
セマンティクス
hint
文の実行計画を選択する場合に、オプティマイザに指示を与えるためのコメントを指定します。
UPDATE
キーワードの直後にパラレル・ヒントを指定した場合、基礎となるスキャンおよびUPDATE
操作の両方をパラレル化できます。
関連項目:
-
ヒントの構文および説明は、「ヒント」を参照してください。
-
パラレル実行の詳細は、『Oracle Database概要』を参照してください。
DML_table_expression_clause
ONLY
句は、ビューのみに適用されます。UPDATE
句のビューが階層に属し、そのどのサブビューの行も変更しない場合は、ONLY
構文を指定します。
関連項目:
「DML_table_expression_clauseの制限事項」および「表の更新: 例」を参照してください。
schema
更新するオブジェクトが含まれているスキーマを指定します。schema
を指定しない場合、オブジェクトは自分のスキーマ内にあるとみなされます。
table | view | materialized_view |subquery
更新する対象となる、表、ビュー、マテリアライズド・ビュー、または副問合せから戻された列の名前を指定します。表に対してUPDATE
文を実行した場合、その表に対応付けられたUPDATE
トリガーが起動します。
-
view
を指定した場合、ビューの実表が更新されます。ビューを定義する問合せに次のいずれかの要素が含まれる場合は、INSTEAD
OF
トリガーを除き、そのビューを更新することはできません。- 集合演算子
DISTINCT
演算子- 集計ファンクションまたは分析ファンクション
GROUP
BY
、ORDER
BY
、MODEL
、CONNECT
BY
またはSTART
WITH
句SELECT
構文のリストにあるコレクション式SELECT
構文のリストにある副問合せWITH
READ
ONLY
が指定された副問合せ- 再帰
WITH
句 - 結合(一部の例外を除く。詳細は、『Oracle Database管理者ガイド』を参照してください。)
-
ビューから、複数の実表は更新できません。
-
また、
WITH
CHECK
OPTION
を指定してビューを作成した場合、実行結果がビューを定義する問合せの条件を満たす場合にのみ、ビューを更新できます。 -
table
またはview
の実表に、1つ以上のドメイン索引列がある場合は、この文によって適切な索引タイプの更新ルーチンが実行されます。 -
読取り専用のマテリアライズド・ビューの行は更新できません。書込み可能なマテリアライズド・ビューの行を更新すると、基礎となるコンテナ表の行も更新されます。ただし、その更新内容は次のリフレッシュ操作によって上書きされます。マテリアライズド・ビュー・グループ内の更新可能なマテリアライズド・ビューの行を更新すると、マスター表の対応する行も更新されます。
関連項目:
-
索引タイプの更新ルーチンの詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
-
更新可能なマテリアライズド・ビューについては、「CREATE MATERIALIZED VIEW」を参照してください。
更新対象のtable
内にあるパーティションまたはサブパーティションの名前またはパーティション・キー値を指定します。パーティション表内の値を更新する場合は、パーティション名を指定する必要はありません。ただし、パーティション名を指定した方が、複雑なwhere_clause
を使用するよりも効果的な場合もあります。
関連項目:
「パーティション表と索引の参照」および「パーティションの更新: 例」を参照してください。
dblink
オブジェクトが格納されているリモート・データベースへのデータベース・リンクの完全名または部分名を指定します。Oracle Databaseの分散機能を使用している場合にかぎり、データベース・リンクを使用してリモート・オブジェクトを更新できます。
dblink
を省略した場合、オブジェクトがローカル・データベース上にあるとみなされます。
ノート:
Oracle Database 12cリリース2 (12.2)以降では、UPDATE
文で、リモートのLOBロケータをバインド変数として指定できます。詳細は、『Oracle Database SecureFilesおよびラージ・オブジェクト開発者ガイド』の「分散LOB」の章を参照してください。
関連項目:
データベース・リンクの参照方法の詳細は、「リモート・データベース内のオブジェクトの参照」を参照してください。
subquery_restriction_clause
subquery_restriction_clause
を使用すると、次のいずれかの方法で副問合せを制限できます。
WITH READ ONLY
WITH READ ONLY
を指定すると、表またはビューを更新禁止にできます。
WITH CHECK OPTION
WITH CHECK OPTION
を指定すると、副問合せに含まれない行を生成する表またはビューの変更を禁止できます。この句をDML文の副問合せ内で使用する場合、FROM
句内の副問合せには指定できますが、WHERE
句内の副問合せには指定できません。
CONSTRAINT constraint
CHECK OPTION
制約の名前を指定します。この識別子を省略した場合は、Oracleによって自動的にSYS_C
n
という形式の制約名が割り当てられます(nはデータベース内で制約名を一意にするための整数)。
table_collection_expression
table_collection_expression
を使用すると、問合せおよびDML操作で、collection_expression
値を表として扱うことができます。collection_expression
には、副問合せ、列、ファンクションまたはコレクション・コンストラクタのいずれかを指定できます。その形式にかかわらず、集合値(ネストした表型またはVARRAY型の値)を戻す必要があります。このようなコレクションの要素抽出プロセスをコレクション・ネスト解除といいます。
TABLE
コレクション式を親表と結合する場合は、オプションのプラス(+)には大きな意味があります。+を指定すると、その2つの外部結合が作成され、コレクション式がNULLの場合でも、外部表の行が問合せで戻されるようになります。
ノート:
以前のリリースのOracleでは、collection_expression
が副問合せの場合、table_collection_expression
をTHE
subquery
と表現していました。現在、このような表現方法は非推奨になっています。
table_collection_expression
を使用して、ある表の行を別の表の行を基にして更新できます。たとえば、四半期ごとの売上表を、年度ごとの売上表にまとめることができます。
t_alias
文中で参照する表、ビューまたは副問合せの相関名(別名)を指定します。DML_table_expression_clause
がいずれかのオブジェクト型属性またはオブジェクト型メソッドを参照する場合、この別名が必要です。
関連項目:
「相関更新: 例」
DML_table_expression_clauseの制限事項
この句には、次の制限事項があります。
-
table
またはview
の実表に、IN_PROGRESS
またはFAILED
とマークされたドメイン索引がある場合は、この文は実行できません。 -
関係する索引パーティションが
UNUSABLE
とマークされている場合は、パーティションに挿入できません。 -
DML_table_expression_clause
の副問合せにはorder_by_clause
を指定できません。 -
UNUSABLE
のマークが付いている索引、索引パーティションまたは索引サブパーティションを指定する場合、SKIP_UNUSABLE_INDEXES
セッション・パラメータがTRUE
に設定されていないかぎり、UPDATE
文は正常に実行されません。
関連項目:
SKIP_UNUSABLE_INDEXES
セッション・パラメータの詳細は、「ALTER SESSION」を参照してください。
update_set_clause
update_set_clause
を使用すると、列の値を設定できます。
column
更新するオブジェクトの列の名前を指定します。update_set_clause
に表の列を指定しない場合、その列の値は変更されません。
column
がLOBオブジェクト属性を参照している場合、まず空またはNULLの値で初期化する必要があります。リテラルで更新はできません。また、UPDATE
以外のSQL文を使用してLOB値を更新する場合は、LOBを含む行を最初にロックしておく必要があります。詳細は、「for_update_clause」を参照してください。
column
が仮想列である場合、ここで指定することはできません。この場合、仮想列の導出元となっている値を更新する必要があります。
column
がパーティション表のパーティション化キーに含まれる場合、別のパーティションまたはサブパーティションに行を移動する列の値を変更すると、行の移動を有効にしないかぎり、UPDATE
は正常に実行されません。「CREATE TABLE」の
「row_movement_clause」または「ALTER TABLE」を参照してください。
また、column
がリスト・パーティション表のパーティション化キーの一部である場合、パーティションのpartition_key_value
リストに存在していない列の値を指定すると、UPDATE
は正常に実行されません。
subquery
更新される行ごとに1行ずつ戻す副問合せを指定します。
-
update_set_clause
で1列のみを指定した場合、副問合せは1つの値のみを戻します。 -
update_set_clause
で複数の列を指定した場合、副問合せは指定した列の数の値を戻します。 -
副問合せが行を戻さなかった場合は、列にはNULLが割り当てられます。
-
subquery
がリモート・オブジェクトを参照する場合、参照がローカル・データベースのオブジェクトにループバックしないかぎり、UPDATE
はパラレルで実行されます。ただし、DML_table_expression_clause
のsubquery
がリモート・オブジェクトを参照する場合は、UPDATE
はシリアルで実行されます。
副問合せ内でflashback_query_clause
を使用すると、過去のデータでtable
を更新できます。この句の詳細は、「SELECT」の「flashback_query_clause」
を参照してください。
関連項目:
-
「SELECT」および「副問合せの使用方法」を参照してください。
-
「CREATE TABLE」の「parallel_clause」を参照してください。
expr
対応する列に割り当てられた新しい値に変換する式を指定します。
ノート:
expr
の構文は、「式」および「オブジェクト表の更新: 例」を参照してください。
DEFAULT
DEFAULT
を指定すると、以前に列のデフォルト値として指定した値を列に設定できます。対応する列に対してデフォルト値を指定していない場合、列にNULLが設定されます。
デフォルト値に対する更新の制限事項
ビューを更新する場合は、DEFAULT
を指定できません。
指定する表に対してOracle Label Securityポリシーが有効になっている場合、UPDATE
文ではDEFAULT
句を使用できません。
VALUE句
VALUE
句を使用すると、オブジェクト表の行全体を指定できます。
VALUE句の制限事項
この句は、オブジェクト表にのみ指定できます。
ノート:
RAW
列に文字列リテラルを挿入する場合、後続の問合せ中にRAW
列にある索引は使用されずに、全表スキャンが行われます。
関連項目:
from_using_clause
この句を使用すると、UPDATE
によって変更される行をフィルタしたり、ターゲット表の列の値を指定することができます。where_clause
で結合条件を指定します。(+)を使用してソース表をターゲット表に外部結合できます。ターゲット表を結合の外部表にすることはできません。
多数の表、ビューおよびインライン・ビューを結合できます。where_clause
で結合条件を指定するか、join_clause
を使用してANSI結合構文でこれらを相互に結合します。
dml_table_expression_clause
およびfrom_clause
で同じ表を指定できます。その場合は、それらが一意の別名を持っている必要があります。
例: 直接結合による更新
この例では、表employees e
と表jobs j
の間の結合条件によって、更新される従業員の行を決定しています。列jobs.max_salary
によって、employees.salary
の新しい値が指定されます。
UPDATE employees e SET e.salary = j.max_salary FROM jobs j WHERE j.job_id = e.job_id;
UPDATE
の直接結合のセマンティクスと制限事項は、SELECT
のfrom_clause
およびwhere_clause
と同じです。ターゲット表には、UPDATE
と同じ制限があります。ターゲット表に対するトリガーは通常どおり起動します。
制限事項
-
ANSI結合構文は、
dml_table_expression_clause
が関与する場合は指定できません。ただし、ANSI結合構文は、FROM
句で指定された表の間では許容されます。右外部結合と完全外部結合は許可されません。 -
UPDATE
が各行を変更できるのは、最大で1回です。結合条件によって同じ行が複数回更新されることになった場合、その文ではORA-30926
エラーが発生します。 -
from_clause
が存在する場合、dml_table_expression_clause
では1つの表、ビューまたはマテリアライズド・ビューのみを指定できます。 -
update_set_clause
の左側は、from_clause
の列ではなく、dml_table_expression_clause
の列である必要があります。 -
FROM
句でlateralビューを使用できますが、更新対象からの列は参照できません。これは、外部結合になる可能性があります。 -
order_by_clause
では、位置による並替えは許可されていません。 -
from_clause
を使用したUPDATE
は、returning_clause
およびerror_logging_clause
をサポートします。 -
ヒント句を使用すると、
from_clause
が関与する結合についてオプティマイザに向けた指示を指定できます。
where_clause
where_clause
を使用すると、指定したcondition
がtrueである行のみを更新するように制限できます。この句を指定しない場合、表またはビューのすべての行が更新されます。condition
の構文は、「条件」を参照してください。
where_clause
は、値を更新する行を決定します。where_clause
を指定しない場合、すべての行が更新されます。where_clause
の条件を満たす行ごとに、update_set_clause
の等号演算子(=)の左側にある列に、演算子の対応する右側の式の値が設定されます。式は行が更新される場合に評価されます。
order_by_clause
ORDER BY
句には、次の制限事項が適用されます。
- 分析ファンクションで使用する場合、
order_by_clause
は、式expr
を取る必要があります。 -
SIBLINGS
キーワードは無効です(これは、階層問合せにのみ関係します) -
position
別名は無効です。
「order_by_clause 」を参照してください
returning_clause
この句は、表、ビュー、マテリアライズド・ビューおよび実表が単一のマテリアライズド・ビューに指定できます。
returning_clause
を指定したDML文を単一行に実行すると、影響を受けた行、ROWIDおよび影響を受けた行へのREF
を使用して列値を取得し、ホスト変数またはPL/SQL変数に格納できます。
returning_clause
を指定したDML文を複数行に実行すると、影響を受ける行が関与している式、ROWIDおよびREF
からの値がバインド配列で返されます。
expr
expr
リストの各項目は、適切な構文で表す必要があります。
INTO
INTO
句を指定すると、変更された行の値を、data_item
リストに指定する変数に格納できます。
data_item
data_item
はそれぞれ、取り出したexpr
値を格納するためのホスト変数またはPL/SQL変数です。
RETURNING
リストの各式については、INTO
リストに、対応する型に互換性があるPL/SQL変数またはホスト変数を指定する必要があります。
表に列c1
とc2
があるとすると、c1
列に対してOLD
を指定できます(例: OLD c1
)。また、列式で参照される列に対してOLD
を指定することもできます(例: c1+OLD c2
)。列に対してOLD
を指定すると、更新前の列値が返されます。列式によって参照される列の場合は、更新前の列値を使用して列式を評価した結果が返されます。
NEW
は、列に明示的に指定することも、更新後に列値を返す式で参照される列に指定することも、列の更新後の値を使用する式の結果に指定することもできます。
列または式に対してOLD
とNEW
の両方を省略すると、更新後の列値または更新後の列値を使用して計算された式の結果が返されます。
制限事項
RETURNING
句には、次の制限事項があります。
-
expr
に次の制限事項があります。-
UPDATE
文およびDELETE
文の場合、各expr
は、単純式または単一セットの集計ファンクション式である必要があります。1つのreturning_clause
内に単純式と単一セットの集計ファンクション式を混在させることはできません。INSERT
文の場合、各expr
は単純式である必要があります。INSERT
文のRETURNING
句では、集計ファンクションはサポートされていません。 -
単一セットの集計ファンクション式を
DISTINCT
キーワードに含めることはできません。
-
-
expr
のリストに主キー列または他のNOT
NULL
列が含まれている場合は、表にBEFORE
UPDATE
トリガーが定義されていると、UPDATE文の実行に失敗します。 -
マルチテーブル・インサートでは
returning_clause
を指定できません。 -
パラレルDMLまたはリモート・オブジェクトにはこの句を使用できません。
-
LONG
型を取り出すことはできません。 -
INSTEAD
OF
トリガーが定義されたビューに対して指定することはできません。
関連項目:
BULK
COLLECT
句を使用してコレクション変数に複数の値を戻す場合は、『Oracle Database PL/SQL言語リファレンス』を参照してください。
error_logging_clause
error_logging_clause
のUPDATE
文での動作は、INSERT
文の場合と同じです。詳細は、「INSERT
」文の「error_logging_clause」を参照してください。
関連項目:
例
表の更新: 例
次の文は、ジョブSH_CLERK
を持つすべての従業員にNULLの歩合を付与します。
UPDATE employees SET commission_pct = NULL WHERE job_id = 'SH_CLERK';
次の文は、Douglas Grantを部門20の管理者に昇格させ、給与を$1,000引き上げます。
UPDATE employees SET job_id = 'SA_MAN', salary = salary + 1000, department_id = 120 WHERE first_name||' '||last_name = 'Douglas Grant';
次の文は、remote
データベースのemployees
表の従業員の給与を増加します。
UPDATE employees@remote SET salary = salary*1.1 WHERE last_name = 'Baer';
次の例は、UPDATE
文の次の構文要素を示します。
-
単一文にまとめた
update_set_clause
の2つの形式 -
相関副問合せ
-
更新された行を制限する
where_clause
UPDATE employees a SET department_id = (SELECT department_id FROM departments WHERE location_id = '2100'), (salary, commission_pct) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b WHERE a.department_id = b.department_id) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700);
このUPDATE
文によって、次の処理が実行されます。
-
ジュネーブまたはミュンヘン(location_idは2900または2700)で働く従業員のみを更新します。
-
これらの従業員の
department_id
をボンベイ(location_id
は2100)の対応するdepartment_id
に設定します。 -
各従業員の給与を、その部門の平均給与の110%に上げます。
-
各従業員の歩合を、その部門の平均歩合の150%に上げます。
パーティションの更新: 例
次の例では、sales
表の1つのパーティションの値を更新します。
UPDATE sales PARTITION (sales_q1_1999) s SET s.promo_id = 494 WHERE amount_sold > 1000;
オブジェクト表の更新: 例
次の文は、「表のコレクション: 例」で作成されたpeople_typ
オブジェクトのオブジェクト表として、people_demo1
とpeople_demo2
の2つを作成します。この例では、people_demo2
から行を選択してpeople_demo1
の行を更新する方法を示します。
CREATE TABLE people_demo1 OF people_typ; CREATE TABLE people_demo2 OF people_typ; UPDATE people_demo1 p SET VALUE(p) = (SELECT VALUE(q) FROM people_demo2 q WHERE p.department_id = q.department_id) WHERE p.department_id = 10;
この例では、SET
句と副問合せの両方で、VALUE
オブジェクト参照ファンクションを使用します。
相関更新: 例
相関副問合せを使用してネストした表の行を更新する例は、「表のコレクション: 例」を参照してください。
UPDATE操作中のRETURNING句の使用方法: 例
次の文は、更新された行の値を戻し、PL/SQL変数bnd1
、bnd2
、bnd3
に結果を格納します。
UPDATE employees SET job_id ='SA_MAN', salary = salary + 1000, department_id = 140 WHERE last_name = 'Jones' RETURNING salary*0.25, last_name, department_id INTO :bnd1, :bnd2, :bnd3;
次の文は、RETURNING句の式で単一セットの集計ファンクションを指定できることを示します。
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 100 RETURNING SUM(salary) INTO :bnd1;
直接結合を使用した更新: 例
次の例では、すべての従業員の給与を職務に応じて最大の給与に設定します。
UPDATE hr.employees e SET e.salary = j.max_salary FROM hr.jobs j WHERE e.job_id = j.job_id;