MySQL-使用SET语句的UPDATE查询取决于前一个SET语句的结果 [英] MySQL - UPDATE query with SET statement dependent on the outcome of the previous SET statement

查看:109
本文介绍了MySQL-使用SET语句的UPDATE查询取决于前一个SET语句的结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

这是我想通过UPDATE语句实现的表格形式.

Here is a tabular representation of what I would like to achieve with an UPDATE statement.

+----+----+---+---+----+----------+---------------+---------------+
| ID | A  | B | C | D  |  Calc A  |    Calc B     |    Calc C     |
+----+----+---+---+----+----------+---------------+---------------+
|  1 |  6 | 5 | 2 | 10 | =[A]-[B] | =[Calc A]/[D] | =[B]/[Calc B] |
|  2 |  8 | 5 | 2 | 10 | =[A]-[B] | =[Calc A]/[D] | =[B]/[Calc B] |
|  3 | 10 | 5 | 2 | 10 | =[A]-[B] | =[Calc A]/[D] | =[B]/[Calc B] |
+----+----+---+---+----+----------+---------------+---------------+

我当前实现此目的的UPDATE语句如下...

My Current UPDATE statement to achieve this is as follows...

UPDATE [EXAMPLE]
SET [Calc A]    = A - B
    , [Calc B]  = [Calc A] / D
    , [Calc C]  = B / [Calc B] 

但是,它没有按预期运行. [Calc A]将在第一个UPDATE上正确计算.但是,[Calc B]将使用[Calc A]中的OLD值而不是我刚刚写入数据库的NEW更新值来进行计算.对于[Calc C]也是如此,它再次引用[Calc B]的OLD值.

However it is not working as intended. [Calc A] will calculate correctly on the first UPDATE. However [Calc B] will calculate using the OLD value in [Calc A] and not the NEW updated value I just wrote to the database. This holds true for [Calc C] which again refers to the OLD value of [Calc B].

如果执行UPDATE语句3次,则数据将正确计算出来.在第一次计算中正确设置了[计算A],然后,在第二次UPDATE中,[计算B]将引用正确的[计算A]更新值,然后,在计算中,[计算C]将参考正确的[计算B]值.第三次更新.

If you perform the UPDATE statement 3 times the data will calculate out correctly. [Calc A] is set correctly in the first calculation, then [Calc B] will reference the correct updated value of [Calc A] in the second UPDATE, then [Calc C] will reference the correct value of [Calc B] in the 3rd UPDATE.

所以我的问题是如何在ONE更新语句中将所有列设置为正确的值?

So my question is how do I set all the columns to their correct value in ONE update statement?

推荐答案

只需独立进行计算:

update [EXAMPLE]
set [Calc A] = A - B,
    [Calc B] = (A - B) / D,
    [Calc C] = B / ((A - B) / D)

这篇关于MySQL-使用SET语句的UPDATE查询取决于前一个SET语句的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆