行集合に連番を割り当てる

主キーが1つの場合

まずは行に連番を割り振ることから始めましょう。表1のような、学生の体重を保持する簡単なテーブルを例に使います。

学生ID順に連番を振ることを考えます。ROW_NUMBER関数が使える環境なら、リスト1のように簡単に記述でき、図1の実行結果が得られます。

SELECT student_id,
       ROW_NUMBER() OVER (ORDER BY student_id) AS seq
    FROM Weights;

student_id   seq
-----------   ----
A100           1
A101           2
A124           3
B343           4
B346           5
C345           6
C563           7

PostgreSQLやMySQLといったROW_NUMBER関数が使えない実装なら、相関サブクエリで置き換えましょう（リスト2⁠）⁠。このサブクエリは、ノイマン型の再帰集合を作り、その要素数をCOUNT関数で数えているのです[2]⁠。主キーであるsutdent_idを比較キーに使用しているため、再帰集合は要素が1つずつ増えていくことが保証されます。それを連番の生成に利用しているのがこのトリックの要諦です。

SELECT student_id,
       (SELECT COUNT(*)
          FROM Weights W2
         WHERE W2.student_id <= W1.student_id) AS seq →ROW_NUMBER を相関サブクエリで代用
  FROM Weights W1;

主キーが2つ以上の場合

さて、それではちょっとテーブルを改変して、主キーを2つの複合キーにしてみましょう（表2⁠）⁠。今度は「クラス、学生ID」で一意になります。こういうケースでも連番を割り振れるよう一般化しましょう。

まず、ROW_NUMBERを使う場合には、特に悩む必要はありません。ORDER BYのキーに追加しましょう（リスト3⁠）⁠。実行結果は図2です。

SELECT class, student_id,
       ROW_NUMBER() OVER (ORDER BY class, student_id) AS seq
  FROM Weights2;

class  student_id seq
----- ------------ ---
    1  100         1
    1  101         2
    1  102         3
    2  100         4
    2  101         5
    2  102         6
    2  103         7

一方、相関サブクエリの場合はどうでしょう。いくつか方法があるのですが、私が最もシンプルと思うのは、複合キーを文字列とみなして連結し、強引に1列のキーに作り変えてしまうことです。そうすれば、あとは先ほどと原理は同じです（リスト4⁠）⁠。

SELECT class, student_id,
       (SELECT COUNT(*)
          FROM Weights2 W2
         WHERE (W2.class || W2.student_id) <= (W1.class || W1.student_id) ) AS seq
  FROM Weights2 W1;

このとき注意が必要なのは、キーが数値や日付など文字型以外のデータ型の場合は、結合の前に文字型へのキャストが必要なことです。このときにデータの桁数や書式を統一しておくことが無用の混乱を避けるうえでも重要です（特に数値の場合は、きっちり前ゼロを埋める形式にしないとうまく連番が並びません⁠）⁠。あとは、キーが3列以上になってもまったく同じように拡張できます。

クラスごとに連番を振る

考え方をちょっと変えて、クラスごとに連番を割り振りたいなら、リスト5、6のようにclass列でパーティションカットしましょう。太字の個所がポイントです。結果はどちらも図3になります。

SELECT class, student_id,
       ROW_NUMBER() OVER (PARTITION BY class ORDER BY student_id) AS seq
  FROM Weights2;

SELECT class, student_id,
       (SELECT COUNT(*)
          FROM Weights2 W2
         WHERE W2.class = W1.class
           AND W2.student_id <= W1.student_id) AS seq
  FROM Weights2 W1;

class  studdent_id  seq
----- ------------ ----------
    1    100                1
    1    101                2
    1    102                3
----------------------------- ←クラスが変わると連番がリセットされる
    2    100                1
    2    101                2
    2    102                3
    2    103                4

連番の列が空の場合

さて、それでは最後にもう一つ。Weights2テーブルを改変して、テーブルに最初から連番を入れるための列は用意されているけど具体的な数字はまだ入力されていない、というケースを考えましょう（表3⁠）⁠。ここに連番をUPDATEするSQLを考えます。

基本的には先ほどの連番を振るクエリの結果をSET句に埋め込む、という素直な発想でよいのですが、ROW_NUMBERを使う場合は、現在の実装ではサブクエリをかませて少し面倒な迂回をする必要があります（リスト7⁠）⁠。相関サブクエリの場合は、特に迂回を意識せず記述できます（リスト8⁠）⁠。結果はどちらも表4のようになります。なお、MySQLは更新SQL内でサブクエリを利用できないため、リスト8はエラーになります。

UPDATE Weights3
   SET seq = (SELECT seq
                FROM (SELECT class, student_id,
                             ROW_NUMBER() OVER (PARTITION BY class ORDER BY class, student_id) AS seq
                        FROM Weights3) SeqTbl
              WHERE Weights3.class = SeqTbl.class
                AND Weights3.student_id = SeqTbl.student_id);

UPDATE Weights3
   SET seq = (SELECT COUNT(*)
                FROM Weights3 W2
               WHERE W2.class = Weights3.class
                 AND W2.student_id <= Weights3.student_id);

更新対象のテーブルに相関名をつけない

どちらのケースでも注意が必要なのは、更新対象のテーブルに「UPDATE Weights3 W」のように相関名をつけてはならない、ということです。標準SQLでは、DELETEやUPDATEのような更新SQLにおいて、更新対象テーブルの相関名の使用が認められていないからです（事実、これをやってしまうとPostgreSQLでは動作しません⁠）⁠。Oracleのように、気を利かせて相関名を使用できるようにしているDBMSもありますが、標準SQLを意識してコーディングする習慣を身につけておくことは大事な心がけです。