CODEWITHSUNDEEP
mysqlsql
Niek
Niek

Reputation: 1030

SQL shift all subsequent rows

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?

Upvotes: 3

Views: 2502

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 4

Related Questions