如何用增量值更新一组记录

Update steps
Set step_num =  (
                    Select Count(*)
                    From steps As T1
                    Where T1.goal_ID = steps.goal_ID
                        And T1.step_ID < steps.step_ID
                    )

编辑

看起来您在MySQL中遇到了一个关于更新包含相关子查询的查询的错误。以上为标准化版本。你可以在MySQL中绕过这些限制,比如:

Update steps
Set step_num =  (
                    Select Count(*)
                    From    (
                            Select T1.step_Id, T1.goal_Id
                            From steps As T1
                            ) As Z
                    Where Z.goal_Id = steps.goal_ID
                        And Z.step_ID < steps.step_ID
                    )

另一个解决方案:

Update steps As T1
    Join    (
            Select T1.step_ID, Count(T2.step_ID) As step_num
            From steps As T1
                Left Join steps As T2
                    On T2.goal_ID = T1.goal_ID
                        And T2.step_ID < T1.step_ID
            Group By T1.step_ID
            ) As T3
        On T3.step_ID = T1.step_ID
Set step_num = T2.step_num

尝试将查询拆分为2,并利用 mysql user defined variables

喜欢

$db->query("SET @counter:=-1");
$db->query(UPDATE steps SET step_num = @counter:=@counter+1 WHERE goal_ID='689' ORDER BY step_ID");