BYPASS_UJVC
仕事でselectupdateをすることがあって、
副問い合わせに対してUPDATE出来たら楽だなと思ってたら
ORA-01779が出てしまったので調べてたら
http://emboss.blog28.fc2.com/blog-entry-13.html
みたいにヒント句で回避できるらしい
ってことで出来ることはわかったのだけど
そもそも 更新対象テーブル:更新データテーブル が
1:N の場合にどういう動きをするかよく分からなかったので試して見た
・更新するテーブル
CREATE TABLE UP_TABLE ( ID NUMBER NOT NULL, NAME VARCHAR2(10), CONSTRAINT UP_TABLE_PK PRIMARY KEY (ID) USING INDEX )
・更新データが入ってるテーブル
CREATE TABLE DATA_TABLE ( UP_ID NUMBER NOT NULL, NAME VARCHAR2(10) )
データは下記の通り
UP_TABLE
ID | NAME |
---|---|
1 | A |
2 | B |
DATA_TABLE
UP_ID | NAME |
---|---|
1 | a |
1 | b |
1 | c |
2 | d |
で、DATA_TABLE.UP_ID と UP_TABLE.ID を結合して、UP_TABLE.NAME を DATA_TABLE.NAME を更新するUPDATE文をBYPASSつかって書いてみる
UPDATE ( SELECT /*+ BYPASS_UJVC*/ UP.NAME ,DT.NAME NAME_UP FROM UP_TABLE UP INNER JOIN DATA_TABLE DT ON DT.UP_ID = UP.ID ) UP SET UP.NAME = UP.NAME_UP
すると 結果は
ID | NAME |
---|---|
1 | c |
2 | d |
UP_TABLE.IDの1でみるとDATA_TABLE.NAMEが「c」で更新される
そもそもUPDATE対象のレコードは2件なのに4件更新されたとメッセージが帰ってきたので
DATA_TABLEと結合した分更新されて、最後に更新されたデータが残っている模様
つまり、1:Nで結合して1に対してNのレコードで更新した時にはN回更新されていて、最後の更新結果がテーブルに残る
ためしに
UPDATE ( SELECT /*+ BYPASS_UJVC*/ UP.NAME ,DT.NAME NAME_UP FROM UP_TABLE UP INNER JOIN DATA_TABLE DT ON DT.UP_ID = UP.ID ORDER BY DT.NAME DESC ) UP SET UP.NAME = UP.NAME_UP
のようにソート順を変更すると結果は
ID | NAME |
---|---|
1 | a |
2 | d |
になった
結局は副問い合わせ内でのソート順で更新されていくので、副問い合わせを使ったUPDATEをする際には
注意が必要みたいですね
まー、そもそも一意にならないselectupdateなんてすることはそうそう無いと思うのですが
ちょっと気になったので調べた結果です
今回のテーブル定義でのSELECTUPDATEだったら
UPDATE UP_TABLE UP SET UP.NAME = (SELECT MAX(DT.NAME) FROM DATA_TABLE DT WHERE DT.UP_ID = UP.ID GROUP BY DT.UP_ID) WHERE UP.ID IN (SELECT DT.UP_ID FROM DATA_TABLE DT)
みたいに、UPDATEしたい情報を一意にしてから
更新すれば事足りますのでこっちを推奨したいところです