CODEWITHSUNDEEP
mysqlsql
Joel
Joel

Reputation: 6107

Moving rows between MySQL tables

I have two tables with the same structure, and both contain an ID field of auto_increment. I would like to move a row from one table to the other.

I know I can use:

INSERT INTO `new_table` VALUES (SELECT * FROM `old_table`)

But that way the ID in new_table will receive the ID of old_table. So I would basically want to copy all fields except for the ID, which needs to get the auto_increment value of new_table. How can I do that?

Thanks!

Joel

EDIT:

Sorry, I should have mentioned in my original post - I would like a way to do it without selecting each field specifically, mostly since I have a lot of fields... I am looking for a more generic approach.

Thanks!

Upvotes: 0

Views: 2567

Answers (4)

Norm
Norm

Reputation: 583

INSERT INTO `new_table` (COLS-I-WANT) 
VALUES (SELECT (OLD-COLS-I-WANT) FROM `old_table`)

Hope this helps.

Upvotes: 0

cherouvim
cherouvim

Reputation: 31903

INSERT INTO `new_table` (name, surname, email) 
  (SELECT name, email, surname FROM `old_table`);

Be careful though when letting the MySQL automatically assign new IDs in case there are relations from other tables you need to move as well.

In such cases I'll move the whole data around by adding a known offset to the ID which will be large enough to avoid ID collisions in the target table. Since the offset is known I'll be able to apply the same offset when moving relations (to the target table) from other tables.

Edit based on question edit: I highly suggest to avoid "the generic approach". It may seem pedantic having to write all the fields but it's the only way to avoid mistakes and, of course, the ID collision problem. If you want to avoid typing the fieldnames (since it's boring and error prone) you can copy paste the fieldnames from show create table new_table.

Upvotes: 3

Chandu
Chandu

Reputation: 82903

Speciy the column names in both INSERT list and SELECT list excluding the ID column

Upvotes: 0

Nishant
Nishant

Reputation: 55866

Do not select id in that case:

INSERT INTO `new_table` (`field2`, `field3`) VALUES (SELECT `field2`, `field3` FROM `old_table`)

Upvotes: 1

Related Questions