Insert a new row into a specific location in MySQL

Question

I have a table of around 30 rows. I need to insert a new row specifically into the 3rd row. I tried manually giving it an 'id' value, hoping the auto-increment would take care of the rest but it's giving me a 'duplicate entry' error for the primary key.

Is there any way around this? It's a one-time fix so I dont need a 'flexible' solution or anything.

Thank you!

Answer 1

execute this update before you insert the row with id=3:

update your_table set id=id+1 where id >= 3;

Answer 2

You could delete all of the records and reinsert them in the order you want them, but that does not necessarily mean you will return them in the correct order.

If you need your results in a certain order, use the order by clause in your SQL.

Why would you need a record placed in the table in a certain order?