2009/02/11(水)20:07
複数テーブルによるUPDATE
Oracleで、複数テーブルによる更新を行う場合、通常以下のように書くことが多い。
UPDATE TBL2 a
SET (F1,F2)=(SELECT F1,F2 FROM TBL1 WHERE KEY=a.KEY);
ただ、このサブクエリによる更新は件数が多いと時間がかかる。
その場合、クエリによる更新が使える。
UPDATE (
SELECT a.F1 AF1,a.F2 AF2, b.F1 BF1, b.F2 BF2
FROM TBL2 a
INNER JOIN TBL1 b ON b.KEY=a.KEY)
SET AF1=BF1,AF2=BF2;
ただし、この書式は結構制約が多い。
以下の書き方はできない。
UPDATE (
SELECT a.F1 AF1,a.F2 AF2, b.F1 BF1, b.F2 BF2
FROM TBL2 a
INNER JOIN TBL1 b ON b.KEY=a.KEY)
SET (AF1,AF2)=(BF1,BF2);
ORA-01767: UPDATE ... SET式は副問合せである必要があります
また、対象テーブルに主キーが設定されていないと、
ORA-01779: キー保存されていない表にマップする列は変更できません
上記は主キーを設定すればうまくいくが、ROWNUM疑似列を使って更新するケースでは、
UPDATE (
SELECT a.F1,b.SEQ
FROM TBL2 a
INNER JOIN
(SELECT KEY,ROWNUM SEQ FROM (SELECT KEY FROM TBL1 ORDER BY KEY DESC)) b ON b.KEY=a.KEY)
SET F1=SEQ;
ORA-01779: キー保存されていない表にマップする列は変更できません
これはROW_NUMBERを使っても同じ。
UPDATE (
SELECT a.F1,b.SEQ
FROM TBL2 a
INNER JOIN
(SELECT KEY,ROW_NUMBER() OVER (ORDER BY KEY DESC) SEQ FROM TBL1) b ON b.KEY=a.KEY)
SET F1=SEQ;
ORA-01779: キー保存されていない表にマップする列は変更できません
この場合は、時間がかかっても以下の方法しかない。
UPDATE TBL2 a
SET F1=(SELECT SEQ FROM (SELECT KEY,ROWNUM SEQ FROM (SELECT KEY FROM TBL1 ORDER BY KEY DESC)) WHERE KEY=a.KEY)