How to update a sequence column using single update statement I have a table, lets call it foo which consist a column priority and has a unique constraint on it.

lets assume below is the current state of foo table :-

mysql&gt; select * from foo;
+-----+----------+
| id  | priority |
+-----+----------+
| 1   |        5 |
| 2   |        6 |
| 3   |        7 |
+-----+----------+


Now I have a requirement where,if I insert a another row with priority value of 6, then it should insert this row and move priority of all the rows which has its value &gt;= to 6 to 1 priority down.

So revised table should look like below :-

mysql&gt; select * from foo;
    +-----+----------+
    | id  | priority |
    +-----+----------+
    | 1   |        5 |
    | 2   |        7 |
    | 3   |        8 |
    | 4   |        6 |
    +-----+----------+


I want to use a sql statement like 

update foo set priority=priority+1 where priority &gt;=6; But obviously it will fail with below error :-


  ERROR 1062 (23000): Duplicate entry '7' for key 'priority'.


Is there is any other way of doing the same thing.
Here is a solution that can help you.

update foo set prio = prio+1 where prio &gt;= 6 order by prio desc;
insert into foo (prio) values(6);