Oracle SQL Summing Values From Two Different Tables

To update a column using a sum of values from other tables that may have nulls, do something like this:

UPDATE Holiday_Reservation R SET Subtotal = 
   NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.In_Flight_ID), 0) +
   NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = R.Out_Flight_ID), 0) +
   NVL((SELECT AC.Acc_PricePn FROM Accommodation AC WHERE AC.Acc_ID = R.Acc_ID), 0);

In a trigger this would look something like this:

CREATE OR REPLACE TRIGGER HR_SUBTOTAL
BEFORE INSERT OR UPDATE ON HOLIDAY_RESERVATION
FOR EACH ROW
BEGIN
   SELECT 
      NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.In_Flight_ID), 0) +
      NVL((SELECT F.Fli_Price FROM Flight F WHERE F.Fli_ID = :new.Out_Flight_ID), 0) +
      NVL((SELECT AC.Acc_PricePn FROM Accomodation AC WHERE AC.Acc_ID = :new.Acc_ID), 0)
      INTO :new.Subtotal
   FROM dual;
END;
/

Using the ranking ROW_NUMBER() function will work. First give row numbers to all rows in both tables, then join using these row numbers, then update:

with 
  oldt as
  ( select fileNo , folder, fileType,
           row_number() over (partition by fileNo, folder
                              order by fileType)
             as rn
    from oldtable
  ),

  newt as
  ( select fileNo , folder, fileType,
           row_number() over (partition by fileNo, folder
                              order by fileType)
             as rn
    from newtable
  ),

  upd as
  ( select 
        n.fileType,
        o.fileType as old_fileType
    from newt n
      join oldt o
      on  n.fileNo = o.fileNo
      and n.folder = o.folder
      and n.rn     = o.rn 
  ) 

update
    upd
set 
    fileType = old_fileType ;

SQLfiddle seems to be giving error for Oracle, so it has been tested in SQL Server only: SQLfiddle-test (but this syntax should be valid for Oracle, too).

Tested in Oracle, the above doesn't work, sadly. I think because statements that start with WITH can only be SELECT statements. Even if the query is rearranged (I tried several rewrites), Oracle throws various errors. The only way I managed to have it working is after adding another column in newtable and a unique constraint on it. Then the following works (nid is the added primary key column).

Tested in Oracle's Live SQL site:

update 
  ( with 
      oldt as
      ( select fileNo , folder, fileType,
               row_number() over (partition by fileNo, folder
                                  order by fileType)
                 as rn
        from oldtable
      ),
    newt as
      ( select fileNo , folder, nid,
               row_number() over (partition by fileNo, folder  
                                  order by fileType)
                 as rn
        from newtable
      ),
    upd as
      ( select 
            n.nid,
            o.fileType as old_fileType
        from newt n
          join oldt o
          on  n.fileNo = o.fileNo
          and n.folder = o.folder
          and n.rn     = o.rn 
      ) 
    select 
        up.fileType,
        ( select upd.old_fileType
          from upd 
          where upd.nid = up.nid 
        ) as old_fileType
    from newtable up
  ) x
set fileType = old_fileType ;