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;

Answer 2

As long as the num field is an autoincrement field it should be as simple as:

INSERT INTO
  yourTable (
    field1,
    field2,
    field3,
    etc
  )
SELECT
  field1,
  field2,
  field3
FROM
  yourSourceTable
ORDER BY
  originalIdField

Answer 3

I would NOT make a field that references a column in another table an auto-increment column.

Even if the column that it references is an auto-increment, I wouldn't make the column auto-increment. It will be difficult to keep the columns in sync. If an insert is rolled back in one table but not the other, you'll be out of sync until you reset the auto_increment value.

If it's a 1 to 1 relationship, feel free to make the column a primary key. That way it will be ordered by the column, and it will ensure unique values. However, if any two columns must match, they should not both be auto-increment, though, they should be of the same type (eg. INTEGER).

For example, here's our original table, where the first column is an auto-increment integer column:

id  customer_name  email_address
---------------------------
1   jsmith         [email protected]
2   bwilliams      [email protected]

If you wanted to split the email_address off to its own table, in a 1 to 1 relationship:

id  email_address
---------------------------
1   [email protected]
2   [email protected]

I would make the first column an integer field and make it the primary key, but it would NOT be an auto-increment column.

To insert values into such a table, you could simply do this:

INSERT INTO table2
  (id, email_address)
SELECT id, email_address
  FROM table1
  ORDER BY id