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
TIP: When you update multiple columns in an UPDATE statement, you need to comma separate the column/value pairs in the SET clause. This UPDATE example would update the supplier_id to 150, the supplier_name to 'Apple' and city to 'Cupertino' where the supplier_name is 'Google'.
The UPDATE statement in SQL is used to update the data of an existing table in database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement.
We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required. we can use the following command to create a database called geeks.
To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With