CODEWITHSUNDEEP
javadatabaseoracle-databaseprepared-statement
Bogie
Bogie

Reputation: 153

Oracle Update Column From Other Column Calculation Result

I have a table passenger with the following structure.

CREATE TABLE passenger (
  id NUMERIC(12,0) NOT NULL,
  station VARCHAR(3), 
  adult NUMERIC(3,0), 
  child NUMERIC(3,0), 
  infant NUMERIC(3,0),
  crew NUMERIC(3,0) DEFAULT 0,
  paying NUMERIC(4,0), 
  non_paying NUMERIC(4,0),  
  total_passenger NUMERIC(3,0), 
  PRIMARY KEY (id)
);

The initial record is as follow.

id  | station | adult | child | infant | crew | paying | non_paying | total_passenger
100 | GWN     | 20    | 4     | 1      | 2    | 24     | 3          | 27

The value of paying field is calculated by summing adult and child value. And, by summing the infant and crew I can get the non_paying value. Then I can sum the paying and non_paying fields to get the total_passenger.

The question is, when I update adult, child, infant, and crew fields with the possibly NULL value for each field, can the database recalculate the paying, non_paying, and total_passenger without the need to update them manually?

I've tried the following query with unexpected result.

UPDATE passenger
SET adult = NVL(NULL, adult),
    child = NVL(6, child),
    infant = NVL(3, infant),
    crew = NVL(NULL, crew),
    paying = adult + child,
    non_paying = infant + crew,
    total_passenger = paying + non_paying
WHERE id = 100;

The NULL keywords comes from the prepared statement parameter, which is the possibly null value. How to update the last three fields automatically?

Upvotes: 2

Views: 281

Answers (2)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

If you are using oracle 11g above then consider of using virtual columns.

create table passenger(id numeric(12, 0) not null,
                       station varchar2(3),
                       adult           numeric(3, 0) default 0,
                       child           numeric(3, 0) default 0,
                       infant          numeric(3, 0) default 0,
                       crew            numeric(3, 0) default 0,
                       paying numeric(4, 0) generated always
                       as(adult + child) virtual,
                       non_paying numeric(4, 0) generated always
                       as(infant + crew) virtual,
                       total_passenger NUMERIC(3, 0) generated always
                       as(adult + child + infant + crew) virtual,
                       primary key(id));

insert into passenger
  (id, station, adult, child, infant, crew)
values
  (1, '1', 2, 3, 4, 5);
insert into passenger (id, station, child, crew) values (2, '2', 7, 8);

select * from passenger;

/*
ID  STATION ADULT CHILD INFANT  CREW  PAYING  NON_PAYING  TOTAL_PASSENGER
1   1       2     3     4       5     5       9           14
2   2       0     7     0       8     7       8           15
*/

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521534

You can use a trigger which does an additional update to the passenger table after an UPDATE happens:

CREATE OR REPLACE TRIGGER update_passenger
AFTER UPDATE OF passenger
FOR EACH ROW
BEGIN
   UPDATE passenger
   SET
       paying = NVL(:NEW.adult, 0) + NVL(:NEW.child, 0),
       non_paying = NVL(:NEW.infant, 0) + NVL(:NEW.crew, 0),
       total_passenger = NVL(:NEW.adult, 0) + NVL(:NEW.child, 0) +
                         NVL(:NEW.infant, 0) + NVL(:NEW.crew, 0)
END;

To use the trigger, you need only to make your updates to passenger as usual. If you do update the adult, child, infant, or crew fields to NULL for a given record, then my trigger will treat a NULL as 0 in the calcuation for the totals.

Upvotes: 1

Related Questions