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;

use a case expression

UPDATE employee
   SET staff_no = 
           CASE depno
                WHEN 1 THEN 'ab123'
                WHEN 2 THEN 'ab321'
                --...
                ELSE staff_no
           END
 WHERE depno IN ( 1, 2 ) -- list all cases here. use a subquery if you don't want to / cannot enumerate 

Cloyd, the description is indeed confusing.

I'll state some assumptions and work on them. Please, shall you consider this worth, comment out and I'll keep editing the answer until we get 'there'. Deal?

Let's go:

• the update statement has as condition facility_id='X'

• the sub-query at the assignment has no condition at all (except pat_seq)

• therefore, I'm assuming and 'forecasting' that:

  • pat_seq is key for identification of a patient

  • both admit_dt and discharge_dt are of type date/time, not just date

  • the facility_id column at patient table keeps track of the several facilities within which the patient has been sent during it's (sad?) stay

  • for any patient, for each facility where it's been sent to, there has to be a row keeping track of date/time of admission and discharging, like:

    • pat_seq: 0015, facility_id: AABB, admit_dt: 15:35, discharge_dt:16:45
    • pat_seq: 0015, facility_id: CCA1, admit_dt:16:45, discharge_dt:17:10
    • pat_seq: 0015, facility_id: AAHD, admit_dt:17:10, discharge_dt: NULL

  • just the admitances are registered, hence for any new admitance registered there has to be an update action upon the imediate previous entry for which the discharge time will be assumed as the admitance time within the next facility

  • bullseye?

Well, for this scenario, you shall do, assuming a batch, scheduled task:

UPDATE patient 
SET discharge_dt = (
            /*
                For every entry past this, retrieve the least admit_dt, which shall identify the moment the pat_seq moved into the next facility
            */
            SELECT MIN(admit_dt)
            FROM patient pat_next_admit
            WHERE pat_next_admit.pat_seq  = patient.pat_seq
              AND pat_next_admit.admit_dt > patient.admit_dt
        ) 
WHERE discharge_dt IS NULL /* No need to process rows already processed! */