MySQL Issue Copying from one table into another directly

Question

I use the following code to copy from one table directly into another:

$transfer = $db->exec("INSERT INTO table2 SELECT * FROM table1 WHERE groupname = '$gname'");

The issue I have, however, is the ID field of both tables do not necessarily match (both auto-increment) and at times this may mean one temporary's tables ID# is higher than the final table.

I use php, pdo and mysql.

Is there any way around this?

Answer 1

Explicity state what columns you want:

"INSERT INTO table2 (`col_1`,`col_2`) SELECT `col_1`, `col_2` FROM table1 WHERE groupname = '$gname'"

Answer 2

"INSERT INTO final_table SELECT * FROM temp_table WHERE temp_table.groupname = '$gname'"

Where's the issue here? If both are auto_increment, and like you said temp_table's id is HIGHER than perm_table, you'll end up getting the right effect.

Answer 3

You should also be aware that MySQL will block inserts to the select table:

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html