0

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> 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 >= to 6 to 1 priority down.

So revised table should look like below :-

mysql> 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 >=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.

  • You should either update before insert the new value or use a BEFORE INSERT TRIGGER. dbfiddle.uk/… – McNets Nov 29 '18 at 18:02
  • @McNets , yeah I am doing update before insert only but my update will only fail as when I try to update priority 6 to 7, it will fail saying 7 already exist. edit in the question as well – Opster Elasticsearch Ninja Nov 29 '18 at 18:07
2

Here is a solution that can help you.

update foo set prio = prio+1 where prio >= 6 order by prio desc;
insert into foo (prio) values(6);
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.