CODEWITHSUNDEEP
phpmysqlincrement
supermitch
supermitch

Reputation: 2962

How to update a group of records with incremental values

I have a table with columns "step_ID", "goal_ID" and "step_number".

step_ID would be the primary, auto-incremented key
goal_ID groups the various steps into specific goals
step_number is currently 0 everywhere, newly created

I want to UPDATE the table so that step_number is set to 0, 1, 2, 3, etc. for every group of similar "goal_ID", ordered by the step_ID.

Said a different way, I'd like to number the steps for a given goal from 0 to however many goals there are, in ascending order of step_ID.

I tried the obvious:

$query = " UPDATE steps SET step_num = step_num + 1 WHERE goal_ID='689' ORDER BY step_ID";

but that just sets all the rows to 1, (as expected.)

I tried also using various subqueries but I got an error saying I couldn't update a table used in the FROM clause.

Thoughts?

Upvotes: 1

Views: 356

Answers (2)

Thomas
Thomas

Reputation: 64645

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
                    )

EDIT

Looks like you are running into a bug in MySQL with respect to Update queries that contain correlated subqueries. The above would be the standardized version. You can work around the limitation in MySQL like so:

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
                    )

Another solution:

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

Upvotes: 1

ajreal
ajreal

Reputation: 47321

Try break the query into 2, and make use of mysql user defined variables

like

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

Upvotes: 2

Related Questions