user3195616 user3195616 - 5 months ago 7
MySQL Question

INSERT INTO table SELECT not giving correct last_id

I have 2 tables with similar columns in MYSQL. I am copying data from one to another with

INSERT INTO table2 SELECT * FROM table1 WHERE column1=smth
. I have different columns as autoincrement and KEY in tables. When I use
mysqli_insert_id
i get the first one rather then last one inserted. Is there any way to get the last one?

Thanks

Answer

There is no inherit ordering of data in a relational database. You have to specify which field it is that you wish to order by like:

INSERT INTO table2 
SELECT * 
FROM table1 
WHERE column1=smth 
ORDER BY <field to sort by here> 
LIMIT 1;

Relying on the order a record is written to a table is a very bad idea. If you have an auto-numbered id on table1 then just use ORDER BY id DESC LIMIT 1 to sort the result set by ID in descending order and pick the last one.


Updated to address OP's question about mysqli_insert_id

According to the Mysql reference the function called here is last_insert_id() where it states:

Important If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

Unfortunately, you'll have to do a second query to get the true "Last inserted id". Your best bet might be to run a SELECT COUNT(*) FROM table1 WHERE column1=smth; and then use that count(*) return to add to the mysqli_insert_id value. That's not great, but if you have high volume where this one function is getting hit a lot, this is probably the safest route.

The less safe route would be SELECT max(id) FROM table2 or SELECT max(id) FROM table2 Where column1=smth. But... again, depending on your keys and the number of times this insert is getting hit, this might be risky.