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
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
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
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.