ROWNUMでUPDATEをしたいのですが・・・。

質問

ＳＱＬ初心者です。
以下のようなテーブルがあり、
ESTAB/PATTERN/NUMBER/CD
1/00/ 1/01
1/00/ 3/02
1/00/ 4/04
1/00/ 5/05
7/00/ 1/01
7/00/ 2/03
7/00/ 3/04
7/00/ 4/05
（主キー＝ESTAB、PATTERN、NUMBER）

SELECT ROWNUM,ESTAB,PATTERN,NUMBER,CD FROM
(SELECT ESTAB,PATTERN,NUMBER,CD
FROM M_PATTERN
WHERE PATTERN='00'
AND ESTAB='1'
ORDER BY NUMBER)
WHERE
PATTERN='00'
AND ESTAB='1'
で検索したところの、ROWNUMでNUMBERを更新したいのですが、

UPDATE M_PATTERN A
SET A.NUMBER=
(SELECT ROWNUM FROM
(SELECT ESTAB,PATTERN,NUMBER FROM M_PATTERN WHERE PATURN='00' AND ESTAB='1' ORDER BY NUMBER) B
WHERE A.NUMBER=B.NUMBER AND A.PATTERN=B.PATTERN AND A.ESTAB=B.ESTAB AND B.PATTERN='00' AND B.ESTAB='1')
WHERE A.PATTERN='00' AND A.ESTAB='1'

としてみたところ、一意制約違反が発生してしまいます。

それとも、このようなUPDATEは無理なんでしょうか？

回答No.1

k_o_r_o_c_h_a_n · ベストアンサー

ROWNUMを使った更新は不可能ではありません。
更新で使用しているサブクエリが、更新すべき値を１コ見つけてくるSQLであれば
正しく処理できます。

NUMBERカラムの再採番が目的だと思うので、

update M_PATTERN X
set
X.NUMBER=
(
select count(*) + 1
from M_PATTERN Y
where X.ESTAB=Y.ESTAB and X.PATTERN=Y.PATTERN and Y.NUMBER<X.NUMBER
)
where X.ESTAB='1' and X.PATTERN='00';

な感じで、ROWNUMを使わない解決が良いと思います。

お礼

回答ありがとうございます。
ご推察の通り、再採番がしたかったんです。

確かに、それを目的とするならこちらの方がＳＱＬがすっきりしていて、わかりやすいですね。
実行させたところ、意図した更新が出来ました。

ありがとうございました。