Oracle: How to update master with newest row from detail table?

Question

We have two tables:

Vehicle:

• Id
• RegistrationNumber
• LastAllocationUserName
• LastAllocationDate
• LastAllocationId

Allocations:

• Id
• VehicleId
• UserName
• Date

What is the most efficient (easiest) way to update every row in Vehicle table with newest allocation? In SQL Server I would use UPDATE FROM and join every Vehicle with newest Allocation. Oracle doesn't have UPDATE FROM. How do you do it in Oracle?

** EDIT **

I am asking for best SQL query for update. I will be using trigger to update data in master table. I know how to write trigger. All I am asking is how to write SQL query to update Vehicle table. Example would be nice. Thank you.

Answer 1

The current setup requires you to use a trigger on the ALLOCATIONS table to maintain the poor decision choice. That said, use:

UPDATE VEHICLE
   SET (LastAllocationUserName, LastAllocationDate, LastAllocationId) =
       (SELECT a.username,
               a.date,
               a.id
          FROM ALLOCATIONS a
          JOIN (SELECT b.vehicleid, 
                       MAX(b.date) AS max_date
                  FROM ALLOCATIONS b
              GROUP BY b.vehicleid) x ON x.vehicleid = a.vehicleid
                                     AND x.max_date = a.date
         WHERE a.vehicleid = VEHICLE.id)

This would be better served by removing the offending columns from the VEHICLE table, and using a view to provide the latest allocation information.

Answer 2

The easiest way to "update" using another table in Oracle is to use MERGE.

MERGE INTO vehicle v
USING (
  -- subquery to get info you need
) s ON (v.id = s.vehicleId)
WHEN MATCHED THEN UPDATE SET 
  username = s.username 
  ...

http://psoug.org/reference/merge.html

Answer 3

From a design point of view, I'd prefer to have the three fields actively maintained on the Vehicle table, with the 'Allocations' populated as a history table (possibly by a trigger). A lot easier to push an update on a parent table down to an insert on a child than the other way around.

Answer 4

UPDATE VEHICLE V
   SET (LastAllocationId, LastAllocationDate, LastAllocationUserName) =
   (SELECT a.id
           ,a.date
           ,a.username
      FROM ALLOCATIONS a
      where a.VehicleId = V.id
        and a.date = ( select max(Last_a.date) from ALLOCATIONS Last_a
                       where Last_a.VehicleId = V.id )
    )

You're right. A View with a history table is slow. There is no such thing as a fast "join to last record". The trigger is the best solution. If you can, use PL to populate the first time. It's easier to understand and mantain.

DECLARE
   Last_date DATE;
   Last_User Varchar2(100);
   Last_ID number;
Begin
FOR V IN ( Select * from VEHICLE )
LOOP
   select max(date) into Last_date 
   from ALLOCATIONS Last_a
   where Last_a.VehicleId = V.id;

   IF Last_date is NULL then 
      Last_User := NULL;
      Last_ID := NULL;
   else
      select Id,UserName into Last_id, Last_user
      from ALLOCATIONS Last_a
      where Last_a.VehicleId = V.id
      and Last_a.date = Last_date;
   END IF;

   UPDATE Vehicle 
     set LastAllocationUserName = Last_User
         ,LastAllocationDate = Last_date
         ,LastAllocationId Last_id
   Where id = V.id;

END LOOP;
End;

Warning: written here, not tested.

Answer 5

Are you looking for the Update inside the Trigger?

CREATE TRIGGER ALLOCATION_I
AFTER INSERT ON ALLOCATION
REFERENCING NEW AS NEW
FOR EACH ROW
Begin

UPDATE Vehicle 
 set LastAllocationUserName = :NEW.Username 
 ,LastAllocationDate = :NEW.date 
 ,LastAllocationId = :NEW.id 
WHERE Id = :NEW.VehicleId;

END;

Answer 6

As indicated by most others: you have a big problem due to your data model. Most code written for this model, will be much more difficult than it needs to be. I've said it by up and downvoting and in some of the comments as well, but it can't be said enough.

If you continue on your path, then the code below demonstrates what needs to be done. Hopefully it scares you :-)

The sample tables:

SQL> create table vehicles (id,registrationnumber,lastallocationusername,lastallocationdate,lastallocationid)
  2  as
  3  select 1, 1, 'Me', sysdate-1, 2 from dual union all
  4  select 2, 2, 'Me', sysdate, 3 from dual
  5  /

Table created.

SQL> create table allocations (id,vehicleid,username,mydate)
  2  as
  3  select 1, 1, 'Me', sysdate-2 from dual union all
  4  select 2, 1, 'Me', sysdate-1 from dual union all
  5  select 3, 2, 'Me', sysdate-1 from dual
  6  /

Table created.

The trigger would have to look into its own table to determine the last allocation. Oracle prevents this type of dirty reads by raising a mutating table error. To circumvent this I create a SQL type and a package:

SQL> create type t_vehicle_ids is table of number;
  2  /

Type created.

SQL> create package allocations_mutating_table
  2  as
  3    procedure reset_vehicleids;
  4    procedure store_vehicleid (p_vehicle_id in vehicles.id%type);
  5    procedure adjust_vehicle_last_allocation;
  6  end allocations_mutating_table;
  7  /

Package created.

SQL> create package body allocations_mutating_table
  2  as
  3    g_vehicle_ids t_vehicle_ids := t_vehicle_ids()
  4    ;
  5    procedure reset_vehicleids
  6    is
  7    begin
  8      g_vehicle_ids.delete;
  9    end reset_vehicleids
 10    ;
 11    procedure store_vehicleid (p_vehicle_id in vehicles.id%type)
 12    is
 13    begin
 14      g_vehicle_ids.extend;
 15      g_vehicle_ids(g_vehicle_ids.count) := p_vehicle_id;
 16    end store_vehicleid
 17    ;
 18    procedure adjust_vehicle_last_allocation
 19    is
 20    begin
 21      update vehicles v
 22         set ( v.lastallocationusername
 23             , v.lastallocationdate
 24             , v.lastallocationid
 25             ) =
 26             ( select max(a.username) keep (dense_rank last order by a.mydate)
 27                    , max(a.mydate)
 28                    , max(a.id) keep (dense_rank last order by a.mydate)
 29                 from allocations a
 30                where a.vehicleid = v.id
 31             )
 32       where v.id in (select column_value from table(cast(g_vehicle_ids as t_vehicle_ids)))
 33      ;
 34    end adjust_vehicle_last_allocation
 35    ;
 36  end allocations_mutating_table;
 37  /

Package body created.

Then 3 database triggers to move the update code from the row level to statement level, thus circumventing the mutating table error:

SQL> create trigger allocations_bsiud
  2    before insert or update or delete on allocations
  3  begin
  4    allocations_mutating_table.reset_vehicleids;
  5  end;
  6  /

Trigger created.

SQL> create trigger allocations_ariud
  2    after insert or update or delete on allocations
  3    for each row
  4  begin
  5    allocations_mutating_table.store_vehicleid(nvl(:new.vehicleid,:old.vehicleid));
  6  end;
  7  /

Trigger created.

SQL> create trigger allocations_asiud
  2    after insert or update or delete on allocations
  3  begin
  4    allocations_mutating_table.adjust_vehicle_last_allocation;
  5  end;
  6  /

Trigger created.

And a little test to verify that it works in a single user environment:

SQL> select * from vehicles
  2  /

        ID REGISTRATIONNUMBER LA LASTALLOCATIONDATE  LASTALLOCATIONID
---------- ------------------ -- ------------------- ----------------
         1                  1 Me 13-05-2010 14:03:43                2
         2                  2 Me 14-05-2010 14:03:43                3

2 rows selected.

SQL> insert into allocations values (4, 1, 'Me', sysdate)
  2  /

1 row created.

SQL> select * from vehicles
  2  /

        ID REGISTRATIONNUMBER LA LASTALLOCATIONDATE  LASTALLOCATIONID
---------- ------------------ -- ------------------- ----------------
         1                  1 Me 14-05-2010 14:03:43                4
         2                  2 Me 14-05-2010 14:03:43                3

2 rows selected.

SQL> update allocations
  2     set mydate = mydate - 2
  3   where id = 4
  4  /

1 row updated.

SQL> select * from vehicles
  2  /

        ID REGISTRATIONNUMBER LA LASTALLOCATIONDATE  LASTALLOCATIONID
---------- ------------------ -- ------------------- ----------------
         1                  1 Me 13-05-2010 14:03:43                2
         2                  2 Me 14-05-2010 14:03:43                3

2 rows selected.

SQL> delete allocations
  2   where id in (2,4)
  3  /

2 rows deleted.

SQL> select * from vehicles
  2  /

        ID REGISTRATIONNUMBER LA LASTALLOCATIONDATE  LASTALLOCATIONID
---------- ------------------ -- ------------------- ----------------
         1                  1 Me 12-05-2010 14:03:43                1
         2                  2 Me 14-05-2010 14:03:43                3

2 rows selected.

Now all you have to do is add some serialization to make it work 100% in a multi user environment. But hopefully the example was scary enough as it is.

Regards, Rob.