mysql - 更新查询后 mysql 中的值重置回 0

Question

这是我的 table 的样子

_____________________________________________________________
|  id  |  val1  |  val2  |  val3  |  val4  |  val5  |  val6  |
|______|________|________|________|________|________|________|
|   1  |  1300  |  1300  |     0  |  2000  |  1300  |     0  |
|______|________|________|________|________|________|________|
|  10  |     0  |     0  |     0  |  2000  |     0  |     0  |
|______|________|________|________|________|________|________|

现在更新

UPDATE points set val5 = CASE WHEN id = 10 THEN val5 + 500 WHEN id = 1 THEN val5 + 500 END WHERE id IN ('10','1')

这是我的 table 的样子

_____________________________________________________________
|  id  |  val1  |  val2  |  val3  |  val4  |  val5  |  val6  |
|______|________|________|________|________|________|________|
|   1  |  1800  |  1800  |     0  |  2000  |  1800  |     0  |
|______|________|________|________|________|________|________|
|  10  |   500  |   500  |     0  |  2000  |   500  |     0  |
|______|________|________|________|________|________|________|
| 157  |     0  |     0  |     0  |   800  |     0  |     0  |
|______|________|________|________|________|________|________|

现在我再次更新(这是它坏的地方)

UPDATE points set val5 = CASE WHEN id = 1 THEN val5 + 500 END, val6 = CASE WHEN id = 10 THEN val6 + 500 END WHERE id IN ('10','1')

我的 table 是这样的

_____________________________________________________________
|  id  |  val1  |  val2  |  val3  |  val4  |  val5  |  val6  |
|______|________|________|________|________|________|________|
|   1  |  2300  |  2300  |     0  |  2000  |  2300  |     0  |
|______|________|________|________|________|________|________|
|  10  |  1000  |     0  |   500  |  2000  |     0  |   500  |
|______|________|________|________|________|________|________|
| 157  |     0  |     0  |     0  |   800  |     0  |     0  |
|______|________|________|________|________|________|________|
| 158  |     0  |     0  |     0  |   800  |     0  |     0  |
|______|________|________|________|________|________|________|

你有没有注意到 val2 和 val5 是如何回到零的，即使我没有告诉它？我错过了什么？

Answer 1

如果表达式不匹配 case 中的任何 when 子句，它返回 null，这不是你看起来的行为想要。相反，您应该在 else 子句中返回列本身:

UPDATE points 
SET    val5 = CASE WHEN id = 1 THEN val5 + 500 ELSE val5 END, -- Notice the else
       val6 = CASE WHEN id = 10 THEN val6 + 500 ELSE val6 END -- Here too
WHERE  id IN (10, 1)