Some like you Some like you - 4 months ago 8
SQL Question

insert on duplicate update get "new" generated id

I want to insert values,and if it exist "insert .. on duplicate update" will update it but it will not update autoincrement val with new inserted one.I heard that insert on duplicate update has some bug WHICH it always generate new id and if exists it will delete it but I want to get exactly this deleted id.(when I use last_inserted_id it just return last id of updated column which is not new inserted one)I want to update id with new inserted query should act like :insert if exists delete exist one and insert new one(with auto increment).I heard that there are replace into but it is so slow and I want just update id with new inserted id.

Answer

With respect, you're playing with fire if you try to carry out fancy update logic with autoincrement columns.

If you need a new autoincrement id in a row when you update it, then just delete the old row and insert the new one like Gordon said.

Under some database workloads that may be slightly slower than using insert on duplicate key update. But unless your table contains at least 100 megarows, or unless you're doing at least 10 of these operations per second all day and all night, the performance difference will be trivial. If you do have that kind of database size or workload, ask your database administrator for advice.