update unique indexed column in mysql

Question

I have a unique indexed column A with integer in it. I'd like it to increment by 1, in Oracle I do: update table set A=A+1 and it worked. But in mySQL, it give me the following error: - MySQL Database Error: Duplicate entry '2' for key 1. I have three rows in the table with value: 1, 2 and 3 individually. I know why it gives me this error. But how do I solve this problem? Thanks.

Answer 1

You receive this error because your UPDATE TABLE SET A = A + 1, when updating the first ROW from 1 to 2 (1+1), it will get conflict with your second ROW, because there already is a ROW with ID = 2.

You have to do it descender from the last ROW to the first, do you have to alter your query to this:

UPDATE TABLE SET ID = ID + 1 ORDER By ID DESC;

The DESC clause will make the updates from the bottom of your table, so it won't find any duplicate ID in his path...

Answer 2

You can do this using an ORDER BY:

update table 
set A=A+1
order by A desc