Can SQL handle something like this initially? Order field

Question

I have a field in my database called "order" and it represents the order in which images are displayed on the page. The order of the images is edited by the user, so after importing them, the user can change them. So let's say that I have these orders ordered like 1,2,3,4,5,6,7,8 .... and the user moves the image in the eighth position to the third position .... is there any SQL update all other entries to move one position up without reading each element in PHP, editing them and then returning them?

So, in this case, the images in positions 1 and 2 remain unchanged .... 8 becomes 3 ... 3 becomes 4, 4 becomes 5, etc.

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, this is a simple update statement:

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

In addition, you, 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

Answer 4

if prio number decreases:

UPDATE table SET prio = prio + 1 WHERE prio <= $new_prio

if it increased:

UPDATE table SET prio = prio - 1 WHERE prio >= $new_prio