insert - MySQL syntax for inserting a new row in middle rows?

Question

mysql sintax for insert a new row in middle rows or wherever we want without updating the existing row, but automatically increment the primary key (id)?

' id | value
' 1  | 100
' 2  | 200
' 3  | 400
' 4  | 500

I want to insert a new row after id 2, with a value = 300. I want the output as below:

' id | value
' 1  | 100
' 2  | 200
' 3  | 300  <-- new row with id (automatic increment)
' 4  | 400  <-- id=id+1
' 5  | 500  <-- id=id+1 

Thanks.

Answer 1

You will have to split it into 2 operations.

START TRANSACTION;

UPDATE table1 SET id = id + 1 WHERE id >= 3 order by id DESC;

INSERT INTO table1 (id, value) VALUES (3, 300);

COMMIT;

Notice that you need the order by in the update statement, so it will start with the highest ids first.

Another idea would be to declare id as decimal(10,1) and insert value 2.5 as id in between 2 and 3.