Let's say I have a data load that inserts 100,000 unique records of a custom object - call it MyObject__c. Each record has a lookup to a Contact record that is populated. I want to iterate over the entire list of 100,000 inserted records and find all of the records of MyObject that look up to the same Contact, and then set a checkbox on all but one record that indicates they are 'duplicates'.
Example: there are 5 record of MyObject (out of 100,000) that all look up to the Contact MyContact (SFID 123456789). After processing the entire list of 100,000 records, I'd have four of the five records have a 'duplicate reference' checkbox marked, with the fifth one having it unchecked. Basically a uniqueness check on the lookup.
What would be the best way to accomplish something like this?
My current thought process is to build an Apex batch job; The batch runs the query to get all MyObject records that need to be processed (100,000) and splits them into a batch. I'm able to get their individual ContactId value they look up to, and I can use that to compare against other MyObject records and see if the Contact SFID lookups match and bulk-process the DML update to the records. Easy.
...But I'm getting hung on on how to get the data for all 100,000 MyObject records to compare against for each batch. Batch splits up the processing, but within each 200-record batch, I still need to get all 100,000 MyObject records that I am processing, read the ContactId they point to and find duplicates.
That is to say, if I am processing record 187 of Batch 1, I still need to compare it against all 100,000 other MyObject records to find the duplicates. I might be overthinking, but I can't find a way to avoid governance limits in this case. a SOQL statement [SELECT contactId FROM MyObject WHERE DataLoadId = myId] would return more than 50,000 rows (since I have 100,000 records that would match). SOSL has similar limitations on returned rows, as do aggregate queries.
How the heck would I do a compare against a large set of data? I feel like the answer is much simpler than I am making it out to be in my head...