Dynamically set column value while inserting in a table in mysql

Question

I need to insert values in a new table from another table with existing data.

INSERT INTO newtable(ordering,customer_id,client) 
(SELECT 1,customer_number,client_name from oldtable);

Current workflow: Id column is set as auto increment so it is not included in the query. This query will set ordering column as 1 in all rows.

What I need: The ordering column must start at 1 then increment by 1 after each row. But I do not want to set this column as auto increment. Can I achieve this through simple query or need to prepare a stored procedure/function? If so, how?

Answer 1

You can use window functions with row number in your select to generate what you want.

INSERT INTO newtable(ordering,customer_id,client) 
 SELECT    
    ROW_NUMBER() OVER(PARTITION BY [id]) AS row_num, customer_number,client_name  
FROM oldtable;