MySQL Delete one row and reorder the rest with the correct sort ID.

Question

I have a table containing a row of rows and sort_order . I was wondering how I can do this, so when I delete the line, I will say that I delete the selected line with sort_order from 7. Then instead of 7, just deleted, I will need to change # 8 to # 7 .. # 9 to # 8 .. # 10 to # 9 .. and from # 11 to # 10.

Is this possible?

Answer 1

If you really need to change these values ​​( sort_orderwill work with gaps), you can

Delete From t
Where sort_order = 7

and then reduce the remaining entries by 1:

Update t
Set sort_order = sort_order - 1
Where sort_order > 7

Answer 2

Like this:

Update tablename 
Set sort_order = sort_order - 1 
where sort_order > 7

Answer 3

DELETE FROM table WHERE sort_order = 7;
UPDATE table SET sort_order = sort_order - 1 WHERE sort_order > 7;

.

Answer 4

. .

- .