|
|
「default」というキーワードでその項目のデフォルト値をセットできる。[2009-12-24]
update テーブル set 項目 = default;
update テーブル a set 項目A = (select 項目B from テーブル b where b.キー = a.キー) ;
update テーブル a set (A1, A2) = (select B1, B2 from テーブル b where b.キー = a.キー) ;
参考: RNKさんのOracle SQL (データの更新 :UPDATE文の基礎)
他のテーブルにデータが存在している行だけを更新するには、existsを使う。[2010-02-04]
(他テーブルにマッチするデータが存在しているかどうかを条件とする)
update 更新対象テーブル u set 〜 where exists ( select * from 他テーブル s where s.key = u.key and S条件 ) and U条件 ;
また、以下のSQLも等価。
(ただし、他テーブルとの結合方法によっては、このUPDATE文はエラーになる。「ORA-01732:
このビューではデータ操作が無効です」)
update (select u.* from 更新対象テーブル u, 他テーブル s where s.key = u.key and S条件 ) set 〜 where U条件 ;
update (select u.* from 更新対象テーブル u, 他テーブル s where s.key = u.key and S条件 and U条件 ) set 〜 ;
件数の小さいテーブルで試した感じでは、実行計画は、2番目と3番目は同じになるっぽい。1番目とはちょっと異なることがあるようだ。(最終的なコストはどちらも同じだったが)
件数の多いテーブルだと、どれも同じ実行計画になった。
また、同じSQLでも、更新対象テーブルと他テーブルのどちらの件数がどれくらい多いかによって実行計画が変わる。
returning句を使うと、UPDATEした際の(更新後の)値をバインド変数に取り込むことが出来る。[2010-07-24]
update テーブル set 項目=値,… returning 項目名… into :バインド変数名… ;
UPDATE本体に指定しなかった項目もバインド変数に代入させることが出来る。
バインド変数はPL/SQLで使う他に、SQL*Plusでも定義することが出来る。
SQL> var v1 number SQL> update EMP set SAL=SAL+1 where ENAME='SCOTT' returning EMPNO into :v1; 1行が更新されました。 SQL> print v1 V1 ---------- 7788
SQL> var v1 number SQL> var v2 number SQL> update EMP set SAL=SAL+1 where ENAME='SCOTT' returning EMPNO,SAL into :v1,:v2; 1行が更新されました。 SQL> print V1 ---------- 7788 V2 ---------- 12931
更新対象が無かった場合(更新件数が0件の場合)は、バインド変数は変更されない。
(NULLになるわけでもない)
複数のレコード(行)が更新対象になった場合、バインド変数がnumber等の単純な型だと代入することが出来ない。
SQL> var v1 number SQL> update EMP set SAL=SAL+1 returning EMPNO into :v1; update EMP set SAL=SAL+1 returning EMPNO into :v1 * 行1でエラーが発生しました。: ORA-24369: 1つ以上のバインド・ハンドルに、必要なコールバックが登録されていません。
PL/SQLでは、バインド変数の型(タイプ)にテーブルを指定してやると、値を取得することが出来る。
declare type NumberList is table of number(4); empnos NumberList; begin execute immediate 'update EMP set SAL=SAL+1 returning EMPNO into :1' returning bulk collect into empnos; end;
参考: Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンスの例7-5 RETURNING BULK COLLECT INTO句を使用した動的SQL
SQL*Plusで出来ない理由は、variableコマンドによる変数定義では独自の型(タイプ)が指定できないから。