Updating a table with the max date of another table

Question

In Oracle 10g, I need to update Table A with data from Table B.

Table A has LOCATION, TRANDATE, and STATUS.

Table B has LOCATION, STATUSDATE, and STATUS

I need to update the STATUS column in Table A with the STATUS column from Table B where the STATUSDATE is the max date upto and including the TRANDATE for that LOCATION (basically, I'm getting the status of the location at the time of a particular transaction).

I have a PL/SQL procedure that will do this but I KNOW there must be a way to get it to work using an analytic, and I've been banging my head too long.

Thanks!

Answer 1

Hi moleboy,

this should get you started (Here the MAX function is the aggregate function and not the analytic function):

UPDATE table_a
   SET status = (SELECT MAX(table_b.status) 
                        KEEP (DENSE_RANK FIRST ORDER BY table_b.statusdate DESC)
                   FROM table_b
                  WHERE table_a.location = table_b.location
                    AND table_b.statusdate <= table_a.trandate);

This will update all the rows in table_a, even if there is no prior row in table_b, updating the status to NULL in that case. If you only want to update the rows in table_a that have a corresponding match in table_b you can add a filter:

UPDATE table_a
   SET status = (SELECT MAX(table_b.status) 
                        KEEP (DENSE_RANK FIRST ORDER BY table_b.statusdate DESC)
                   FROM table_b
                  WHERE table_a.location = table_b.location
                    AND table_b.statusdate <= table_a.trandate)
 WHERE EXISTS (SELECT NULL
                 FROM table_b
                WHERE table_a.location = table_b.location
                  AND table_b.statusdate <= table_a.trandate);

Answer 2

This is a version with the analytic function. It update all the rows in table_a as shown. To update a specific row, add a filter.

update table_a t1 set status = (
       select distinct
              first_value(t2.status) over (partition by t1.location, t1.trandate order by t2.statusdate desc)
       from temp_b t2 
       where t1.location = t2.location
       and t2.statusdate <= t1.trandate );