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.
解决方案
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...