How to update the order column using single update statement while order changed-CodePudding

lets assume below is the current state of the table 'test_group':

mysql> select * from test_group;

 ---- ------------ --------------- 
| id | group_name | display_order |
 ---- ------------ --------------- 
|  1 | A          |             1 |
|  2 | B          |             2 |
|  3 | C          |             3 |
|  4 | D          |             4 |
|  5 | E          |             5 |
 ---- ------------ ---------------

Now, I want to insert the record with order 5 before order 3, So revised table should look like below :

mysql> select * from test_group;

 ---- ------------ --------------- 
| id | group_name | display_order |
 ---- ------------ --------------- 
|  1 | A          |             1 |
|  2 | B          |             2 |
|  3 | C          |             4 |
|  4 | D          |             5 |
|  5 | E          |             3 |
 ---- ------------ ---------------

The original order like below :

group_name "A" is NO.1
group_name "B" is NO.2
group_name "C" is NO.3
group_name "D" is NO.4
group_name "E" is NO.5

Now, I want to insert the record with order 5 before order 3, so the modified order like below:

group_name "A" is NO.1
group_name "B" is NO.2
group_name "E" is NO.3
group_name "C" is NO.4
group_name "D" is NO.5

My sql statement like below :

update
    test_group
set
    display_order =(case
        when display_order >= 3
        and display_order <5 then display_order   1
        when id = 5 then 3
        else display_order
    end)
where
    display_order in (3,4,5);

Although my sql statement can be successfully executed, is there a better way, I think my statement is very stupid

CodePudding user response：

UPDATE test
SET pos = CASE pos WHEN @pos_from THEN @pos_to
                   ELSE pos   SIGN(@pos_from - @pos_to)
                   END
WHERE pos BETWEEN LEAST(@pos_from, @pos_to) AND GREATEST(@pos_from, @pos_to);

fiddle with some comments.

PS. The query does not check the positions values values. You may add this if needed.

CodePudding user response：

Although my sql statement can be successfully executed, is there a better way, I think my statement is very stupid

update
    test_group
set
    display_order =(case
        when display_order >= 3
        and display_order <5 then display_order   1
        when id = 5 then 3
        else display_order
    end)
where
    display_order in (3,4,5);

this is okay too :

update
    test_group
set
    display_order = if(display_order =
    #{posFrom}, #{posTo}, display_order   SIGN(#{posFrom} - #{posTo}))
where
    display_order between LEAST(#{posFrom}, #{posTo}) and GREATEST(#{posFrom}, #{posTo});