在同一查询中两次更新列

I want to know what this query will do:

我想知道这个查询会做什么：

UPDATE users SET
PaymentAmount = PaymentAmount + TempPaymentAmount,
PaymentDuration = PaymentDuration + TempPaymentDuration,
TempPaymentAmount = NULL,
TempPaymentDuration = NULL
WHERE UserID = 1234

You'll notice that I am trying to copy a value from temp column to the original column and nullify it at the same time. I am wondering if the query will do what I expect.

您会注意到我正在尝试将值从临时列复制到原始列并同时使其无效。我想知道查询是否会做我期望的。

4 个解决方案

#1

For instance values in row (UserId=1234) are following:

例如，行（UserId = 1234）中的值如下：

PaymentAmount = 10
PaymentDuration = 30 
TempPaymentDuration = 40
TempPaymentAmount = 50

After the query execution:

查询执行后：

PaymentAmount = PaymentAmount + TempPaymentAmount = 60
PaymentDuration = PaymentDuration + TempPaymentDuration = 70
TempPaymentAmount = NULL
TempPaymentDuration = NULL

MySQL 12.2.10. UPDATE Syntax

MySQL 12.2.10。更新语法

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. For example, the following statement sets col1 to one more than its current value:

如果从表中访问要在表达式中更新的列，UPDATE将使用列的当前值。例如，以下语句将col1设置为比其当前值多一个：
UPDATE t1 SET col1 = col1 + 1;

Also an interestign point:

也是一个互动点：

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

以下语句中的第二个赋值将col2设置为当前（更新的）col1值，而不是原始col1值。结果是col1和col2具有相同的值。此行为与标准SQL不同。
UPDATE t1 SET col1 = col1 + 1, col2 = col1;

#2

Should work, according to the docs (http://dev.mysql.com/doc/refman/5.0/en/update.html):

应该工作，根据文档（http://dev.mysql.com/doc/refman/5.0/en/update.html）：

Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

单表UPDATE分配通常从左到右进行评估。对于多表更新，无法保证以任何特定顺序执行分配。

So, if you run it, it should perform all SET queries one by one, thus using the original value first, then nullifying it.

因此，如果你运行它，它应该逐个执行所有SET查询，因此首先使用原始值，然后使其无效。

#3

Why don't you give it a try and execute it?

你为什么不试一试并执行它？

The answer is yes: When you use values from columns that you modify in the same query, the database engine will take the unmodified values while calculating the updated values.

答案是肯定的：当您使用在同一查询中修改的列中的值时，数据库引擎将在计算更新值时采用未修改的值。

#4

It should do.. My understanding of what is actually happening is:#

它应该做..我对实际发生的事情的理解是：＃

UPDATE users SET
:New.PaymentAmount = :Old.PaymentAmount + :Old.TempPaymentAmount,
:New.PaymentDuration = :Old.PaymentDuration + :Old.TempPaymentDuration,
:New.TempPaymentAmount = NULL,
:New.TempPaymentDuration = NULL
WHERE UserID = 1234

#1