Mysql update after specific sort number

Question

I'm currently doing some mySQL/PHP but i'm stuck with one small problem. If the user deletes lets say id 4, there's gonna be a gap between sorting 3 and 5. So my thinking is that i need to -(minus) all sort numbers after sort 3 with one.

How do i do that in Mysql? :) I thought it would be something like following, but realize it didn't worked.

UPDATE menucard_category_content SET number_value = number_value-1 AFTER number_value=$sortnr

Table layout looks like:

ID - NAME - SORT - IMAGE
1, pizza, 1, url
2, pizza, 2, url
3, pizza, 3, url
4, pizza, 4, url
7, pizza, 5, url
9, pizza, 6, url

Regards, Simon

Mark Byers · Accepted Answer · 2012-11-22 10:35:21Z

1

You can do it with an UPDATE statement:

UPDATE yourtable
SET sort = sort - 1
WHERE sort > 3

answered Nov 22, 2012 at 10:35

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

simon Over a year ago

Off course Mark, why didn't i think doing that solution. I will check it out, and let you know if it works :)

Collectives™ on Stack Overflow

Mysql update after specific sort number

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related