Sql – ORACLE: Updating multiple columns at once

oraclesql

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

Best 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 "INV_TOTAL is not updating, only the inv_discount"?

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.

Related Topic