mysqlprimary-keyincrement

Increase part of primary key by 1

I have a SQL table where the primary keys are: user_name and idx. The idx value is a simple integer starting at 1. However, I want to increase idx by 1 for a specific user. Typically I'd do something like this:

UPDATE my_table SET idx = idx + 1 WHERE user_name = 'Bob';

But the problem is, Bob has idx values of 1, 2, and 3. So when I try that query, I get the following error message:

ERROR 1062 (23000): Duplicate entry 'Bob-2' for key 'my_table.PRIMARY'

So my question is - how do I increase idx by 1 for Bob when it's part of the primary key?

Solution

  • Add ORDER BY idx DESC

    UPDATE my_table
SET idx = idx + 1
WHERE user_name = 'Bob'
ORDER BY idx DESC;

    See example