SQL ORACLE：一次更新多列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13539334/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE: Updating multiple columns at once
提问by David Garcia
I am trying to update two columns using the same update statement can it be done?
我正在尝试使用相同的更新语句更新两列可以完成吗？
IF V_COUNT = 9 THEN
UPDATE INVOICE
SET INV_DISCOUNT = DISC3 * INV_SUBTOTAL
, INV_TOTAL = INV_SUBTOTAL - INV_DISCOUNT
WHERE INV_ID = I_INV_ID;
DBMS_OUTPUT.PUT_LINE ('YOU QUALIFY FOR A DISCOUNT OF 30%');
The issue is that the INV_TOTALis not updating, only the inv_discount
问题是INV_TOTAL没有更新，只有inv_discount
DISC3 = 0.3I.E 30% discount, so what ever the sub_total is will be multiplied by 0.3 and that's the value for INV_discount
DISC3 = 0.3IE 30% 折扣，因此 sub_total 将乘以 0.3，这就是 INV_discount 的值
INV_TOTAL = sub_total - discount
INV_TOTAL = sub_total - 折扣
INV_ID|INV_DATETIME |INV_SUBTOTAL|INV_DISCOUNT| INV_TOTAL
----------|------------------------------|------------|------------|-----------
100|14-NOV-12 09.40.06.918000 | $.00| $.00| $.00
101|18-MAR-12 10.03.00.000000 | $.00| $.00| $.00
102|18-MAR-12 10.15.00.000000 | $.00| $.00| $.00
103|18-MAR-12 10.55.00.000000 | .00| .00| .00
104|18-MAR-12 10.38.00.000000 | $.00| $.00| $.00
105|12-JUN-12 15.15.00.000000 | $.00| $.00| $.00
106|06-AUG-12 12.13.00.000000 | $.00| $.00| $.00
107|04-MAY-12 09.15.00.000000 | $.00| $.00| $.00
108|29-NOV-12 13.16.00.000000 | .00| .00| .50
109|18-MAR-12 10.37.00.000000 | .00| .00| .00
108is suppose to be 20% of 25, the discount amount is correct but the inv_total is not, it should be $20, not $22.50
108假设是 25 的 20%，折扣金额是正确的，但 inv_total 不正确，应该是 20 美元，而不是 22.50 美元
109is suppose to be 30% of 50 the discount amount is correct but inv_total should be $35
109假设是 50 的 30% 折扣金额是正确的但 inv_total 应该是 $35
103calculates fine, which is 10% discount
103算罚款，也就是10%的折扣
回答by APC
It's perfectly possible to update multiple columns in the same statement, and in fact your code is doing it. So why does it seem that "INV_TOTAL is not updating, only the inv_discount"?
在同一个语句中更新多个列是完全可能的，实际上您的代码正在这样做。那么为什么看起来“INV_TOTAL 没有更新，只有 inv_discount”？
Because you're updating INV_TOTAL with INV_DISCOUNT, and the database is going to use the existingvalue of INV_DISCOUNT and not the one you change it to. So I'm afraid what you need to do is this:
因为您正在使用 INV_DISCOUNT 更新 INV_TOTAL，并且数据库将使用INV_DISCOUNT的现有值，而不是您将其更改为的值。所以恐怕你需要做的是：
UPDATE INVOICE
SET INV_DISCOUNT = DISC1 * INV_SUBTOTAL
, INV_TOTAL = INV_SUBTOTAL - (DISC1 * INV_SUBTOTAL)
WHERE INV_ID = I_INV_ID;
Perhaps that seems a bit clunky to you. It is, but the problem lies in your data model. Storing derivable values in the table, rather than deriving when needed, rarely leads to elegant SQL.
也许这对你来说有点笨拙。是的，但问题在于您的数据模型。将可派生值存储在表中，而不是在需要时派生，很少会导致优雅的 SQL。
回答by Ajith Sasidharan
I guess the issue here is that you are updating INV_DISCOUNT and the INV_TOTAL uses the INV_DISCOUNT. so that is the issue here. You can use returning clause of update statement to use the new INV_DISCOUNT and use it to update INV_TOTAL.
我想这里的问题是您正在更新 INV_DISCOUNT 而 INV_TOTAL 使用 INV_DISCOUNT。所以这就是问题所在。您可以使用 update 语句的返回子句来使用新的 INV_DISCOUNT 并使用它来更新 INV_TOTAL。
this is a generic example let me know if this explains the point i mentioned
这是一个通用示例，让我知道这是否解释了我提到的观点
CREATE OR REPLACE PROCEDURE SingleRowUpdateReturn
IS
empName VARCHAR2(50);
empSalary NUMBER(7,2);
BEGIN
UPDATE emp
SET sal = sal + 1000
WHERE empno = 7499
RETURNING ename, sal
INTO empName, empSalary;
DBMS_OUTPUT.put_line('Name of Employee: ' || empName);
DBMS_OUTPUT.put_line('New Salary: ' || empSalary);
END;
