MySQL - 基于子查询的计数器更新 [英] MySQL - Update with counter based on subquery

问题描述

我正在尝试对 mysql 表进行更新:

I'm tring to make an update on a mysql table:

 PrimaryId | SecondaryId | Order
-----------+-------------+-------
     1     |      1      |   0
     2     |      1      |   0
     3     |      2      |   0
     4     |      3      |   0
     5     |      3      |   0
     6     |      3      |   0

到:

 PrimaryId | SecondaryId | Order
-----------+-------------+-------
     1     |      1      |   1
     2     |      1      |   2
     3     |      2      |   1
     4     |      3      |   1
     5     |      3      |   2
     6     |      3      |   3

在具有相同次要 ID 和 0 顺序的行上.到目前为止，我尝试选择要在子查询中更新的值，并使用 max()+1

on rows that have the same secondary id and an Order of 0. so far i tried to select the values to update in an subquery, and update the rows with max()+1

...遗憾的是，这将不起作用，因为不允许选择与更新在同一个表上工作.有没有办法做到这一点?

...sadly, this won't work since the select is not allowed to work on the same table as the update. is there a way to do that?

推荐答案

试试这个:

UPDATE Table1 t1
JOIN (
   SELECT `PrimaryId`,
          `SecondaryId`,
          (SELECT count(*)
           FROM Table1 t1
           WHERE t1.`SecondaryId` = t.`SecondaryId`
            AND t1.`PrimaryId` <= t.`PrimaryId`
           ) `Order`
   FROM Table1 t
) t2
ON t1.`PrimaryId` = t2.`PrimaryId`
SET t1.`Order` = t2.`Order`
;

演示 --> http://www.sqlfiddle.com/#!2/6f2102/1

这篇关于MySQL - 基于子查询的计数器更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！