MySQL, SELECT then INSERT and INCRIMENT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

MySQL, SELECT then INSERT and INCRIMENT

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Post 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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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 
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Post 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.
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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]. ;)
User avatar
ed209
Forum Contributor
Posts: 153
Joined: Thu May 12, 2005 5:06 am
Location: UK

Post by ed209 »

thanks for the help. I have just used the auto increment for now, but I'll give that other method a try.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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;
Post Reply