I'm trying to move selected data from one table to another. The following works apart from the destination table is not incrementing the ID (I'm not using auto increment for that field).
How can I increase the the value of field_1_id for each select > insert? I'm guessing SQL doesn't loop through each SELECT match an insert correspondingly?
It won't increment the autoincrement field if there is a value for the autoincrement field in the INSERT. You need to exclude the autoincerement field or make the value NULL.
The problem is that I am not using auto increment hence the need for creating my own increment - but MAX(target_table.id) + 1 doesn't work, that is the problem I am having.
The SELECT part of the query is run first, then the insert happens. So the MAX(table1.field1ID) will only look at what is currently in table1 before the insert, it will not recalculate for each row inserted.
Why not just use an autoincrement field, this will do exactly what you are trying to achieve (adding 1 to every record inserted).
make you table_1.id_field an autoincrement type, then
I use a php framework that manages it's own row increments so none of my tables have auto increment set. This query is a one off to move data around to a new schema so I could add auto increment to that table temporarily then turn it off again, but it would be good to know if it can be done another way.
thanks.