自動連番でカラムを更新したい

質問

いつもお世話になります。
あるテーブルのカラムに、「ある順番に並び替えた上で」自動連番をふって更新させたいのですが、上手くいきません。
当初はシーケンスを作成して試みたのですが、並び替えた上で連番をふることができず無作為な連番になってしまいました。
そこで、色々考えまして以下のように作成しましたが、、、
update tableA set colC = (select rownum from tableA order by colA, colB);
「ORA-00907:右カッコがありません」が出てきます。
文法が間違っておりますでしょうか？？
それとも他に何か良い方法がありましたらご教授いただけますでしょうか？

回答No.8

uresiiwa · ベストアンサー

No.5です。
変数に値を入れただけになっているため、UPDATEしないといけないですね。以下のような感じになると思います。（コンパイル確認までできていませんのでご了承ください。）
この方法ですとあえてシーケンスをつかわなくてもよいかもですね。

declare
seq_no number := 1;
cursor cur1 is
select colA, colB, colC from tableA
order by colA, colB
-- ☆追加（カーソルをFOR UPDATE付きで宣言する）
FOR UPDATE;

begin
for cur_rec in cur1
loop

--☆変更（現在レコードにUPDATEをかける）
--cur_rec.colC := seq_no;
Update tableA set colC = seq_no where current of cur1;

seq_no := seq_no + 1;
end loop;
end;
/

お礼

カーソルで更新かけたことなかったもので。。
PL/SQLで上手くいけました。
どうもありがとうございました！

回答No.7

webuser

>平行してPL/SQLでも試みているのですが

また思い付きですが、
もうここはシンプルに
フェッチで１件づつ読みながらカレントレコードを更新するのはどうですか？

DECLARE
wk_colA tableA.colA%TYPE;
wk_colB tableA.colB%TYPE;
wk_colC tableA.colC%TYPE;
wk_連番 tableA.colC%TYPE;
CURSOR カーソル名 IS SELECT colA, colB, colC FROM tableA FOR UPDATE order by colA, colB;
BEGIN
wk_連番 の０クリア
OPEN カーソル名;
LOOP
wk_連番 の加算

FETCH カーソル名 INTO wk_colA, wk_colB, wk_colC;

UPDATE tableA SET wk_colC = wk_連番 WHERE CURRENT OF カーソル名;

EXIT WHEN カーソル名%NOTFOUND;
END LOOP;
END;
/

ところで、colA, colBでレコードはユニークになるのですか？

お礼

PL/SQLでいけました。
色々ありがとうございました。

回答No.6

jamshid6

#3です。
>ただカラム数が多いせいか、レスポンスが非常に悪いようです。
このような作業はワンタイムで行うものかと思っていましたが。。
当然レスポンスは悪いです。ORDER BYを何回やるか考えてみたらわかるでしょう。

レコードの並べ替えはどうしても全件必要ですから、１クエリでやるとすればこれ以外の手はないはずです。
（UPDATE句にJOINしたサブクエリを記述する場合、キーが保存されている必要があるので、ROW_NUMBERでもROWNUMでも、今回の例では使えません）

カラムを個別に指定すれば以下の通り b は必要ありませんが、レスポンス改善の効果はあまり期待できません。
UPDATE TABLEA a
SET COLC=(SELECT SEQ FROM
(SELECT ROWNUM SEQ,COLA,COLB FROM (SELECT COLA,COLB FROM TABLEA ORDER BY COLA,COLB))
WHERE COLA=a.COLA AND COLB=a.COLB)

一旦、ROWNUMも保存したワークテーブルに入れてJOIN更新するしかないでしょう。
CREATE TABLE TEMP1 AS
SELECT ROWNUM AS SEQ,COLA,COLB FROM (SELECT COLA,COLB FROM TABLEA ORDER BY COLA,COLB);

ALTER TABLE TEMP1 ADD CONSTRAINT PK1 PRIMARY KEY (COLA,COLB);

UPDATE (SELECT a.COLC,b.SEQ FROM TABLEA a INNER JOIN TEMP1 b ON b.COLA=a.COLA AND b.COLB=a.COLB) t
SET t.COLC=t.SEQ;

お礼

しょっちゅう行うわけではないですが、３～４回行うためある程度のレスポンスが必要なのです。
色々ありがとうございました。

回答No.5

uresiiwa

すいません、さっきのSQLちょっと書き間違えてしまいました。
以下のように訂正します。

（誤）
select seq_XX.nextval, VW.XX from (SELECT * FROM TBL order by TBL.ZZZ);

（正）
select seq_XX.nextval, VW.XX from (SELECT * FROM TBL order by TBL.ZZZ) VW;

お礼

ご回答ありがとうございます!

平行してPL/SQLでも試みているのですが、初心者であまりよくわかっていないのですが、以下のような感じでしょうか?

declare
seq_no number := 1;
cursor cur1 is
select colA, colB, colC from tableA
order by colA, colB
begin
for cur_rec in cur1
loop
cur_rec.colC := seq_no;
seq_no := seq_no + 1;
end loop;
end;
/

ただ、実行してみると上手く更新かかりません。
ご教授いただけますでしょうか??

回答No.4

uresiiwa

>当初はシーケンスを作成して試みたのですが、並び替えた上で連番をふることができず無作為な連番になってしまいました。

上記文面から推測ですが、この失敗の原因は、
select seq_XX.nextval, TBL.XX from TBL order by TBL.ZZZ;
と書いてしまったためではないでしょうか？
上記の書き方ですと、ZZZの並び順とシーケンスの番号順が無関係になります。

以下のように書けばZZZの順にシーケンス採番することが可能です。
select seq_XX.nextval, VW.XX from (SELECT * FROM TBL order by TBL.ZZZ);

「当初は」のところに立ち戻って上記方法で可能かどうかご確認ください。

どうしてもUPDATEでやりたいならば、単文のUPDATE文だとシーケンスで連番振るのはちょっと無理なような気がしますので、PL/SQLで処理を組むことをオススメします。
カーソルで並び替えながらSELECTして、一行づつシーケンス番号でUPDATEする要領です。あまり複雑な単文をヒネリ出すよりも、このほうが読みやすく実装もカンタンです。

回答No.3

jamshid6

そもそもROWNUMは、抽出後並び替え前のレコード番号ですから、
SELECT COLA,COLB,ROWNUM FROM TABLEA ORDER BY COLA,COLB
では想定した連番は返ってきません。

したがって、TABLEAに主キーがあるなら（たとえばCOLA,COLBが主キーなら）、
UPDATE TABLEA a
SET COLC=(SELECT SEQ FROM
(SELECT ROWNUM SEQ,b.* FROM (SELECT * FROM TABLEA ORDER BY COLA,COLB) b)
WHERE COLA=a.COLA AND COLB=a.COLB)

主キーもないようなテーブルなら、＃２さんのようにワークテーブルを使いますが、

CREATE TABLE TEMP1 AS SELECT COLA,COLB FROM TABLEA;
TRUNCATE TABLE TABLEA;
INSERT INTO TABLEA SELECT COLA,COLB,ROWNUM FROM (SELECT * FROM TEMP1 ORDER BY COLA,COLB);

CREATE TABLE TEMP1 AS SELECT COLA,COLB FROM TABLEA;
DROP TABLE TABLEA;
CREATE TABLE TABLEA AS SELECT COLA,COLB,ROWNUM AS COLC FROM (SELECT * FROM TEMP1 ORDER BY COLA,COLB);

お礼

ご回答ありがとうございます。
ただカラム数が多いせいか、レスポンスが非常に悪いようです。

> (SELECT ROWNUM SEQ,b.* FROM (SELECT * FROM TABLEA ORDER BY COLA,COLB) b)

↑の「b.*」は必要なのでしょうか?
試しに消して見ると、「単一行副問い合わせにより2つ以上の行が戻されます」とエラーになってしまいました。
せめて、カラム指定すると早くなるものでしょうか?

回答No.2

webuser

思い付きですが、、、

手元にオラクルが無いので試せませんので保証はできませんが、
一旦テンポラリテーブル作るというのはどうですか？

create table temp1 as select colA, colB from tableA;
drop table tableA;
create table tableA as select colA, colB, rownum "colC" from temp1 order by colA, colB;

お礼

ご回答ありがとうございます！
試してみましたがCreate table時にはorder byは指定できないようですね。。
他に思いつきで、、、
update (select colC from tableA order by colA, colB) wk
set wk.colC = rownum;
「このビューではデータ操作が無効です」が出てきます。
他に何かありませんでしょうか？
よろしくお願い致します。

回答No.1

webuser

update tableA set colC = (この値は複数件でなく、１件にならなければならない);