ORACLE Updating multiple columns at once

Question

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 TOTAL is not updating  only the inv discount  DISC3   0 3 I E 30  discount  so what ever the sub total is will be multiplied by 0 3 and that s the value for INV discount  INV TOTAL   sub total - discount      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             80 00         8 00       72 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             25 00         5 00       22 50        109 18-MAR-12 10 37 00 000000             50 00        15 00       45 00   108 is suppose to be 20  of 25  the discount amount is correct but the inv total is not  it should be  20  not  22 50  109 is suppose to be 30  of 50 the discount amount is correct but inv total should be  35  103 calculates fine  which is 10  discount

User · Accepted Answer

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  quot INV TOTAL is not updating  only the inv discount quot   Because you re updating INV TOTAL with INV DISCOUNT  and the database is going to use the existing value of INV DISCOUNT and not the one you change it to   So I m afraid what you need to do is this  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

User · Answer

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   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

[sql] ORACLE: Updating multiple columns at once

Examples related to sql

Examples related to oracle