![Increase part of primary key by 1 [duplicate]](/_next/image?url=https%3A%2F%2Fcdn.sanity.io%2Fimages%2F80wy3gkl%2Fproduction%2F54e30bbcb677f725db7afda2ea2f51db97c37e46-1201x631.png&w=3840&q=75)
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'
How do I increase idx by 1 for Bob when it's part of the primary key?
Answer
Add ORDER BY idx DESC
UPDATE my_table
SET idx = idx + 1
WHERE user_name = 'Bob'
ORDER BY idx DESC;