mysql - 在同一查询中更新列两次

Question

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

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

您会注意到我试图将一个值从临时列复制到原始列并同时使它无效。我想知道查询是否会按照我的预期进行。

Answer 1

例如行 (UserId=1234) 中的值如下:

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

查询执行后:

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

---

MySQL 12.2.10. UPDATE Syntax

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 t1 SET col1 = col1 + 1;

还有一个有趣的地方:

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.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;