为什么更新查询条件失败

Question

i have sql table as follow

id      position
------------
1       2
2       1
3       3

Now i wants to perform action that return result as follow

 id     position
    ------------
    1       1
    2       2
    3       3

So how can i do ?

Answer 1

Use two variables to store the positioncolumn values of ids having 1 and 2. Then use a CASE expression to update the table accordingly.

Query

set @a := (select `position` from `your_table_name` where id = 1);
set @b := (select `position` from `your_table_name` where id = 2);

update `your_table_name`
set `position` = (
    case `id` when 1 then @b else @a end
)
where `id` < 3;