更新mysql中的唯一索引列 [英] update unique indexed column in mysql

问题描述

我有一个唯一的索引列A，其中有整数.我希望它增加1，在Oracle中，我这样做:更新表集A = A + 1并奏效.但是在mySQL中，它给了我以下错误: -MySQL数据库错误:密钥1的条目"2"重复.表中有三行，其值分别为:1、2和3.我知道为什么它会给我这个错误.但是我该如何解决这个问题呢?谢谢.

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.

推荐答案

您收到此错误，因为UPDATE TABLE SET A = A + 1将第一个ROW从1更新为2(1 + 1)时，它将与第二个ROW发生冲突，因为已经有ID = 2的ROW.

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.

您必须将其从最后一个ROW降到第一个ROW，是否必须将查询更改为此:

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;

DESC子句将从表的底部进行更新，因此它将在其路径中找不到任何重复的ID ...

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

这篇关于更新mysql中的唯一索引列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！