Assign increasing integer values to a column in mysql database

We have an application where we have some learning path and each learning path is associated with some courses. The association between learning path and courses is stored in a mysql table. Now we also want to store course position in learning path to course mapping, so I have added another column ‘position’ in the table and I want to backfill existing data in it by a query in such a way that each course is assigned a number starting from 1 in any random way(Number should start from 1 for every learning path). For example,

LP1|C1         LP1|C1|1
LP1|C2   ==>   LP1|C2|2
LP1|C3         LP1|C3|3
LP2|C1         LP2|C1|1
LP2|C5         LP2|C5|2

Answer

If you are running MysQL 8.0, you can just use row_number() for this:

select 
    learning_path, 
    course, 
    row_number() over(partition by learning_path order by course) position
from mytable

If you need an update query:

update mytable t
inner join (
    select 
        learning_path, 
        course, 
        row_number() over(partition by learning_path order by course) position
    from mytable
) t1 on t1.learning_path = t.learning_path and t1.course = t.course
set t.position = t1.position

In earlier versions, one option is:

update mytable t
inner join (
    select 
        learning_path, 
        course, 
        (select 1 + count(*) from mytable t1 where t1.learning_path = t.learning_path and t1.course < t.course) position
    from mytable t
) t1 on t1.learning_path = t.learning_path and t1.course = t.course
set t.position = t1.position