CODEWITHSUNDEEP
mysql
soon beer
soon beer

Reputation: 13

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

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

Upvotes: 1

Views: 41

Answers (2)

soon beer
soon beer

Reputation: 13

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});

Upvotes: 0

Akina
Akina

Reputation: 42632

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.

Upvotes: 1

Related Questions