尝试增加日期字段时，MySQL会出现重复输入错误？

I am reading in data from an XML file. Due to an error at the source it is one day out, so after loading into the database I use this SQL statement to increment the date.

我正在读取XML文件中的数据。由于源上的错误是一天，所以在加载到数据库后，我使用此SQL语句来增加日期。

UPDATE 2011_electricity SET DATE = DATE_ADD( DATE, INTERVAL 1 DAY )

Last week it worked fine, however now I get an error:

上周它工作正常，但现在我收到一个错误：

MySQL said: 

#1062 - Duplicate entry '2011-07-20' for key 1

I have one primary key on the data field. This is how the database looks:

我在数据字段上有一个主键。这是数据库的外观：

                date        energy  daynum
        2011-06-29  0.05    4197
        2011-07-19  0.20    4219
        2011-07-20  17.07   4220
        2011-07-21  11.56   4221
        2011-07-22  18.18   4222
        2011-07-23  24.92   4223
        2011-07-24  10.56   4224
        2011-07-25  12.68   4225
        2011-07-26  10.06   4226
        2011-07-27  19.72   4227
        2011-07-28  19.02   4228
        2011-07-29  17.92   4229
        2011-07-30  14.49   4230
        2011-07-31  10.84   4231
        2011-08-01  13.38   4232
        2011-08-02  14.86   4233

I cannot see any duplicate there, so do not understand the error, is there a better way to carry out mysql code to increment the day by 1?

我在那里看不到任何重复，所以不明白错误，是否有更好的方法来执行mysql代码将一天增加1？

2 个解决方案

#1

This is a problem on how MySQL's UPDATE works, row by row as p.cambell explained. Another way to bypass this issue, is to explicitely tell the engine how to order the updates (another MySQL quirk):

正如p.cambell所解释的那样，这是MySQL的UPDATE如何工作的问题。绕过这个问题的另一种方法是明确地告诉引擎如何订购更新（另一个MySQL怪癖）：

UPDATE 2011_electricity 
SET DATE = DATE_ADD( DATE, INTERVAL 1 DAY )
ORDER BY DATE DESC

Rule of thumb: If you want to increase the PKs (or other Unique Key), order by descending. If you want to decrease the PKs, order by ascending.

经验法则：如果您想增加PK（或其他唯一键），请按降序排序。如果您想减少PK，请按升序排序。

#2

It sounds like you're trying to update the PK values on the table. Aside: suggest finding another PK for this table. Here's what's happening.

听起来你正在尝试更新桌面上的PK值。旁白：建议为此表寻找另一个PK。这是正在发生的事情。

Row by row:

逐行：

update the PK for 2011-06-29 to be 2011-06-30. This succeeds as there's no other row with that PK value.
更新2011-06-29的PK为2011-06-30。这成功，因为没有其他行具有该PK值。
update the PK for 2011-07-19 to be 2011-07-20. This fails as there's already another row with that PK value. The pre-existing row's PK hasn't been incremented yet. We've now violated the PK constraint.
将2011-07-19的PK更新为2011-07-20。这失败了，因为已经有另一行具有该PK值。预先存在的行的PK尚未增加。我们现在违反了PK限制。

Suggest modifying your approach to either:

建议修改您的方法：

Delete all your new data in the table, and reload from source as you're doing now.
删除表中的所有新数据，并从现在开始重新加载源。
leverage a staging/temp table if deleting isn't an option. You might want to swap out rows selectively as per your needs.
如果不是选项，则利用临时/临时表。您可能希望根据需要有选择地换出行。

#1