MySQL syntax for inserting a new row in middle rows?
insert, mysql
Solution
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.
Problem
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.