Can SQL handle something like this natively? Order field

Question

I have a field in my database called "order" and it represents the order in which images appear on the page. The order of the images is user editable so after they are imported the user can change them. So lets say I have these images ordered as so 1,2,3,4,5,6,7,8....and the user moves the image in the 8th position to the 3rd position....is there a way in SQL to update all other records to move one position up without having to read each item in PHP, edit them, and then put them back?

So in this case the images in position 1 and 2 stay the same....8 becomes 3... 3 becomes 4, 4 becomes 5 and so on

Answer 1

you can try +1

like

1.) update tablename set `order` = `order` + 1 where `order` >= 3
2.) update tablename set `order` = 3 where `order` = 9; ie ( 8 + 1 )

Answer 2

Yes, it's a simple update statement:

UPDATE images SET order = order + 1 WHERE order > 3 and order < 8

Apart from that, you do of course need to move the original row from 8 to 3.

Answer 3

update table set pos = case when pos = 8 then 3 else pos + 1 end where pos >= 3