updateを1行ずつ実行したい。

質問

掲示板のようなシステムを開発しています。
登校時に記事に投稿ナンバーが割り当てられますが、記事を削除したときにこの投稿ナンバーを更新させたいのです。

どういうことかといいますと、投稿ナンバーが1～10の記事があったとします。そこれ4～7の記事を削除すると、残りの記事数は6つとなり、記事ナンバーは上から1,2,3,8,9,10となり、連番ではなくなってしまいます。今後このデータベースを利用するに当たって、この数字が連番になっていないと困ります。

そこで、記事が削除されたときに投稿ナンバーをチェックし、間違っていたら数値を更新させたいのです。先ほどの例ですと、8,9,10を4,5,6にしたいのです。
検索してみた結果、データの更新（上書き）にはUPDATE構文を使えば良いそうですが、これを1行ずつ実行させる方法が分かりません。limit文は引数が1つしか使えないため、0番目からn番目の要素すべてを一気に検出・置換してしまいます。

このような問題にどのようにして対応すればよいのか、皆様の知恵をお貸しください。

回答No.3

HTTP500

UPDATE TBL
SET id = id - 削除した連番合計数
WHERE id > 削除したID;

というのでどうでしょうか？

回答No.2

galluda

がると申します。
時々拝見するタイプの質問なのでちと気になるのですが。
その「投稿ナンバー」は、テーブルのプライマリキーになってないでしょうか？
それ次第で大分かわるので。

ただ、いずれにしても「かならず連番である必要がある」設計は、それ自体にミスがあることが多いです。

回答No.1

yambejp

過去にさかのぼって毎回レコードを直すのは
非効率的だと思います。たとえば古い１件の修正を
するためにその後の登録分何万件もの修正が発生する
可能性もあり、その運用方法は致命的かもしれません。

とりあえず、どうしてもというなら
set @no=0;
update `table` set `投稿ナンバー`=@no:=@no+1
などでいけそうですが、表示順の保障がないので、
もうすこし工夫が必要かもしれません。

そもそも「数字が連番になっていないと困る」状況と
いうのが理解できません。単に序列がひつようなだけ
であれば連番の意味はあまりないかと。

またどうしても連番にしたいということであれば
連番を管理するための専用のテーブルを用意し、
検索時にINNER JOINするのがもっとも効率的かと
おもいます。

補足

回答ありがとうございます。
おっりゃるとおり、確かに場合によっては何万件もの修正が必要になるかもしれませんが、あくまで掲示板製作の実験ですので、投稿数に上限（最高でも300件とか）をつけることによってカバーできるものと思い、製作しておりました。

CGIの掲示板などで投稿が削除された後、欠番が何らかの方法で埋められて、結果記事ナンバーが連番になっている（表示順は違う場合あり）ものを見かけたので、どのようにして実行させているのか気になって質問しました。

お教えいただいた方法も試してみようと思います。