How to insert a row in a table between two existing rows in Sql

Question

I have a table with 9 records in it but i want to insert a row in between of 5th and 6th record.

Why? Do you not want to order your results when you query the table instead? — Rowland Shaw, Feb 07 '12 at 13:35
What for? Physical ordering of rows in table storage means nothing to performance and in most databases what you ask is impossible. — Sergey Kudriavtsev, Feb 07 '12 at 13:36
I think he wants to move, for example, song in the list about one position before another song? ... — Martin., Feb 07 '12 at 13:37

score 14 · Answer 1 · answered Feb 07 '12 at 13:40

14

if you insist on

UPDATE mytable SET id = id + 1 where id > 5 ORDER BY id ASC

insert into mytable (id,..) values (6,...)

answered Feb 07 '12 at 13:40

Haim Evgi

6

You need to ORDER BY id DESC so that it increments from the bottom up and avoid duplicate id errors. – postelrich Jul 22 '15 at 14:46
@postelrich that's so right! – Roberto Sepúlveda Bravo Oct 04 '16 at 03:21
If anyone is looking to do this in sqlite, refer https://stackoverflow.com/a/54758778/6671004 – Shrey Garg Apr 17 '19 at 08:55

score 3 · Answer 2 · answered Feb 07 '12 at 13:40

In general, you don't insert a row at a specific location in a table.

If the row "order" is significant and has some special semantic, have the data reflect that with a proper column in the table structure.

Then use a SELECT ... ORDER BY ... to get rows sorted.

2 Answers2