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

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

[sql] ORACLE: Updating multiple columns at once

Examples related to sql

Examples related to oracle