Insert sequential numbers based on another field - MySQL

Question

There is a similar question Insert sequential number in MySQL

I want to insert sequential numbers to the table, but based on another field. I have two columns page_numner and parent, so the rows with same parent should have page_number as consequtive numbers. If parent changes, the page should start from 1 again and increase by one.

I was thinking to use smth like this

SELECT @i:=0;
SELECT @p:=0;

UPDATE my_table AS t SET page_number = CASE
WHEN @p = t.`parent` THEN @i:=@i+1 
ELSE 1 -- assign current parent to @p ??
END

but, it cant figure out how to assign the new parent into @p for the else case.

Please note, that I am trying to achieve this with pure mysql (if possible of course)

Thanks

Gordon Linoff · Accepted Answer · 2015-10-10 17:09:26Z

1

You can do what you want with this code:

set @p := -1;
set @i := 0;

UPDATE my_table t
    SET page_number = (CASE WHEN @p = t.`parent` THEN @i := @i+ 1 
                            WHEN (@p := t.parent) = NULL THEN NULL -- never happens
                            ELSE @i := 1
                       END)
    ORDER BY t.parent;

Unfortunately, MySQL doesn't allow both ORDER BY and JOIN in the same UPDATE query. If it did, you could initialize the variables in the query.

Note the second condition just does the assignment. = NULL never returns TRUE.

edited Oct 10, 2015 at 17:09

answered Oct 10, 2015 at 16:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

defines Over a year ago

MySQL absolutely supports ORDER BY and JOIN in the same query.

Gordon Linoff Over a year ago

@defines . . . I thought the context was obvious. That was referring to an UPDATE: dev.mysql.com/doc/refman/5.6/en/update.html.

dav Over a year ago

thanks for the answer, this almost worked, but one small issue I am trying to figure out. lets say for parent=1 it assigned the page numbers from 1 to 15, for the parent=2 the first page number is 1, but the second is 16, which I guess because the var @i ended with 15, is there a way to reset it to 0 again when the parent changes ? thanks

Gordon Linoff Over a year ago

@dav . . . Oops, I missed that really important assignment. I just fixed it.

dav Over a year ago

it worked !, learnt smth new(and important) ))) , thanks a lot

Collectives™ on Stack Overflow

Insert sequential numbers based on another field - MySQL

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related