SQL shift all subsequent rows

Question

I have a table where each row has a primary key ID (ordered ASC), which is just a number. Is there a way to insert a row between two others by first shifting all of the rows below it by one?

Answer 1

Yes you can like you want to insert row with id 2 you can do so

CREATE TABLE Table1
    (id INT,`test` varchar(10))
;

INSERT INTO Table1
    (id,`test`)
VALUES
    (1,'val'),
    (2,'val'),
    (3,'val')
;

Demo table has 3 records and id 2 is already assigned now you want to add row for id 2 and increment all the ids by 1

update Table1 
set id =id+1
where id >1
ORDER BY id DESC;

INSERT INTO Table1
    (id,`test`)
VALUES
    (2,'my val');

In update query ORDER BY id DESC is necessary for primary key to ignore the duplicate entry error

SELECT * FROM Table1 order by id

Fiddle Demo