mysql - Inserting into table in order

Question

How can I insert values into a table (MySQL) in the following manner:
On all the rows of a table, in order of ID column (PK), insert incrementing number in column 'num'?
For example if the table had 3 rows , with Ids 1,5,2, I want ID 1 to get num=1, ID 2 to get num=2 and ID 5 to get num=3.

EDIT I will explain why I (think I) need this:
I am trying to split a column off a table into a separate table with a 1-to-1 relation. I thought I would get all the values in order of ID and insert them into the new table, with an auto-incrementing PK. then I know that, in order of ID, the values for the new reference column in the original table will be auto-incrementing numbers. So I want to insert them in that order. I hope this is clear.

Answer 1

i am currently not in front of sql database engine and cannot therefore submit fully verified sql code. however if your num field is not an autoincrement field than do something like this:

CREATE TEMPORARY TABLE temp_table_x (
    num int auto_increment primary key,
    reference_id int
);

INSERT temp_table_x (reference_id)
SELECT id FROM source_table ORDER BY id;

UPDATE source_table st 
    SET st.num = x.num
FROM temp_table_x x
WHERE reference_id = id;