[Solved] Why should update Query condition fails

by


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;

solved Why should update Query condition fails