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;