Oracle Remove Duplicates and Update Rows in a Table with a Value from Another Table

Question

In my associates table I have 4,978 people with at least 1 duplicate.

asscssn     | count(*)
---------     --------
123456789      8
987654321      5
234567890      5

Each duplicate for a person has a unique id (asscid) in the associates table.

asscid | asscssn
------  -------
53492    987654321
53365    987654321
53364    987654321
52104    987654321
50185    987654321

My cases table has a case tied to each asscid

docketnumber | asscid
-----------    -------
2010JV0000     53492
2010JV1111     53365
2010JV2222     53364
2010JV3333     52104
2010JV4444     50185

I would like to take each person that has a duplicate, grab the latest asscid that person has from the associates table and update the cases table. Results would be:

  docketnumber | asscid
    -----------    -------
    2010JV0000     53492
    2010JV1111     53492
    2010JV2222     53492
    2010JV3333     53492
    2010JV4444     53492

Answer 1

If I understand correctly, you want:

select c.docketnumber, max_asscid
from cases c join
     (select a.*, max(asscid) over (partition by asscssn) as max_asscid
      from associations a
     ) a
     on c.asscid = a.asscid;

This assumes that the "latest" asscid is the one with the largest value. If you have another column that specifies the ordering (such as a date), then you can use first_value() instead.

EDIT:

If you actually want to update the data:

update cases c
    set assc_id = (select max_asscid
                   from (select a.*, max(asscid) over (partition by asscssn) as max_asscid
                         from associations a
                         where asscssn is not null
                        ) a
                   where a.asscid = c.asscid
                  );