連番欠番アルゴリズム

質問

AccessのMDBです。

u b
i a
d n
---
1 4
2 1
3 3
4 6

uidは主キーです。
banはユニークにしたいフィールドです。
banが3であるレコードをインサートする前に、banのユニークを保つため
uid==1とuid==3のレコードのbanをインクリメントしたいです。
以下のようになればよいのですが、プロシージャ無しで軽く実行できないでしょうか？

u b
i a
d n
---
1 5
2 1
3 4
4 6
5 3

回答No.2

jamshid6 · ベストアンサー

目的の更新を掛けるだけなら以下のクエリで可能と思います。
UPDATE Tbl1 SET BAN = BAN+1
WHERE BAN BETWEEN 3 AND (SELECT MIN(BAN) FROM Tbl1 WHERE BAN NOT IN (SELECT BAN-1 FROM Tbl1) AND BAN>=3);

スタンドアロンで使用しているなら、特に問題はないでしょうが、
このUPDATEを掛けてからINSERTを行うまでには、該当レコードすべてをロックしなければ更新の競合により整合性が維持できなくなるリスクが高まりますし、レコードを追加すればするほど更新対象件数も増えていきます。逆にロックを掛けるとUPDATE自体が失敗する可能性も高くなります。
データ構造自体は考え直した方がいいかもしれません。

回答No.4

jamshid6

お世話になります。
>質問者さんの要望では影響のないものには加算を行わないようですよ
そのようですね。
ですので、クエリは「自分の番号に１を足したものが存在しなくなるまでの間の番号に１を足す」というBetween条件にしてみました。

＃NVLはつけた方がよかったかもしれませんね、

回答No.3

CHRONOS_0

>目的の更新を掛けるだけなら以下のクエリで可能と思います。
自分より大きいもの全てに1を加算するのなら可能ですが
質問者さんの要望では影響のないものには加算を行わないようですよ
（id4の例)

回答No.1

CHRONOS_0

>プロシージャ無しで軽く実行できないでしょうか？
どこで書き直しを終了するかの判断は
再帰的プログラムでないと出来ないようですから
(影響の及ぶ範囲が変動する）
プロシージャを使わないと出来ないでしょうね