Page 1 of 1
MySQL, SELECT then INSERT and INCRIMENT
Posted: Thu Mar 22, 2007 4:27 pm
by ed209
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?
Code: Select all
INSERT INTO table_1 ( field_1_id, field_1 )
SELECT MAX(table_1.field_1_id)+1, able_2.field_1
FROM table_1, table_2
GROUP BY table_2.table_id_field
Posted: Thu Mar 22, 2007 7:07 pm
by Christopher
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.
Posted: Fri Mar 23, 2007 3:42 am
by ed209
thanks for the reply.
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.
Posted: Fri Mar 23, 2007 4:22 am
by mikeq
No it wont work,
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
Code: Select all
INSERT INTO table_1 ( field_1 )
SELECT table_2.field_1
FROM table_2
Posted: Fri Mar 23, 2007 4:36 am
by ed209
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.
Posted: Fri Mar 23, 2007 10:32 am
by mikeq
Not with MySQL alone.
You would need to do a php script, iterate through your selected records, pick up the last inserted ID then insert the next record.
The method you just mentioned is the best way with MySQL alone.
Posted: Fri Mar 23, 2007 10:55 am
by timvw
You can select a 'rownumber' as following:
Code: Select all
SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, mytable t;
Using that technique, you could try to generate the ids as following:
Code: Select all
INSERT INTO table_1 (field_1_id, field1)
SELECT
@rownum := @rownum + 1,
t2.field_1
FROM
(SELECT @rownum := (SELECT MAX(table_1.field_1_id) + 1) r,
table_2 as t2;
feyd | It's [syntax]. 
Posted: Fri Mar 23, 2007 3:34 pm
by ed209
thanks for the help. I have just used the auto increment for now, but I'll give that other method a try.
Posted: Fri Mar 23, 2007 5:15 pm
by timvw
Anyway, here's what would have worked:
Code: Select all
SET @id := (SELECT MAX(id) + 1 FROM table2);
INSERT INTO table2 SELECT @id := @id + 1, table1.name FROM (SELECT @id) as id, table1 as table1;