MYSQL how to get next auto increment when inserting with select

Question

I have two tables who's structure is identical, but the content is different. The first column of each table is an auto increment id.

What I am trying to do is insert some rows from one table into the other. However, the id column should set automatically in the target table and not be copied from the source.

So far I am using something like:

INSERT INTO table_1
    SELECT
       id,
       column_2,
       column_3,
       column_4
    FROM table_2
    WHERE column_4 = 'a value'

I cannot just select the ID from table 2 as shown above as this creates duplicate ids in table 1.

I have tried replacing 'id' with: (SELECT MAX(id)+1 FROM table_1) AS id. That gets the correct id, but doesn't increment it for each row to be inserted.

I have tried (SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA_TABLES WHERE SCHEMA_TABLE = 'table_1') AS id but that just fails saying 'Unable to resolve column AUTO_INCREMENT'

I have tried LAST_INSERT_ID() as id. This actually worked the first time I run the statement, but further attempts fail with the message 'Duplicate entry 875 for key PRIMARY'

Finally, I have tried to just leave the column out altogether but i then get an error message saying the number of columns do not match.

Can anyone shed any light onto this? I should point out that I need pure MySQL. I am not in the context of PHP.

Thanks

Answer 1

You have to specify the columns to insert to, then you can leave it out:

INSERT INTO table_1 (column_2, column_3, column_4)
  SELECT column_2, column_3, column_4
    FROM table_2
    WHERE column_4 = 'a value'