To visualize the issue, let say I have this table:

With column book_running_no just added.

I want to update the table, setting column book_running_no with a sequence number for each book_id and expect the result to be like this:

book_running_no = book_id 2 digit running number

I found the running number solution here, but now I need to custom the number generation for each book_id. The actual table has more than 800k records.

Thanks in advance for your assistance.

CodePudding user response：

UPDATE test t1
SET book_running_no = CONCAT( book_id,
                              LPAD(CASE WHEN book_id = @id
                                        THEN @num := @num   1
                                        ELSE @num := 1 
                                        END, 2, 0), 
                              LEFT((@id := book_id), 0)
                              )
ORDER BY book_id;

fiddle