mysql procedure to update numeric reference in previous rows when one is updated
- by markcial
There's a table like this one
______________________
| id | title | order |
|----------------------|
| 1 | test1 | 1 |
|-----|--------|-------|
| 2 | test2 | 2 |
|-----|--------|-------|
| 3 | test3 | 3 |
|-----|--------|-------|
| 4 | test4 | 4 |
'----------------------'
when i introduce in my mysql shell a single update to a row
$sql UPDATE `table` SET order=1 WHERE id=3;
And then procedure or method resamples order column in the before update lower values to get its order renewed like this
______________________
| id | title | order |
|----------------------|
| 1 | test1 | 2 |
|-----|--------|-------|
| 2 | test2 | 3 |
|-----|--------|-------|
| 3 | test3 | 1 |
|-----|--------|-------|
| 4 | test4 | 4 |
'----------------------'
Any help would be appreciated, thanks!