mysql - 通过对自身执行查询来更新表

Question

我想根据从自身选择的查询更新表。从这样的表格开始:

__________________________
| id | uid |  uid_seq_no |
--------------------------
| 1  | a   |     NULL    |
| 2  | a   |     NULL    |
| 3  | b   |     NULL    |
| 4  | a   |     NULL    |
| 5  | b   |     NULL    |
| 6  | b   |     NULL    |
| 7  | a   |     NULL    |
| 8  | c   |     NULL    |
--------------------------

我想将 uid_seq_no 更新为范围为 uid 的行的序列号，这样最终结果将是:

__________________________
| id | uid |  uid_seq_no |
--------------------------
| 1  | a   |       1     |
| 2  | a   |       2     |
| 3  | b   |       1     |
| 4  | a   |       3     |
| 5  | b   |       2     |
| 6  | b   |       3     |
| 7  | a   |       4     |
| 8  | c   |       1     |
--------------------------

我尝试执行如下查询:

UPDATE keySeq a
  SET uid_seq_no=(
    SELECT IFNULL(uid_seq_no,0)+1 FROM keySeq b
      WHERE a.uid = b.uid AND uid_seq_no IS NOT NULL
      ORDER BY id
      LIMIT 1
    );

但我得到:表“a”被指定了两次，既作为“UPDATE”的目标，又作为单独的数据源。

我还尝试像这样执行它:

UPDATE keySeq a
  SET uid_seq_no=(
    SELECT n FROM (
      SELECT IFNULL(uid_seq_no,0)+1 AS n FROM keySeq b
        WHERE a.uid = b.uid AND uid_seq_no IS NOT NULL
        ORDER BY id
        LIMIT 1
      ) AS T
    )

但是我得到了 Unknown column 'a.uid' in 'where clause'。可能是因为子子查询无法访问查询范围。

现在我没主意了。

示例表:http://sqlfiddle.com/#!9/e3f3b6/1

Answer 1

你可以使用这个查询:

UPDATE keySeq
left join  (
  select a.id, (SELECT count(1) + 1 FROM keySeq b 
                     where b.uid = a.uid and  b.id<a.id) Rank
  from keySeq a
) xQ on xQ.Id=keySeq.id
SET keySeq.uid_seq_no=xQ.Rank;