I have several tables which correspond to fiscal_years (2017, 2018, 2019, 2020, etc). Each of these tables contain a record_id and an owner_id - with the record_id being unique within a given year. Each of the fiscal_year tables contain 6-8 million rows, and the owners table contains 1 million rows. Each of these fiscal_year tables contain a process_date field meant to represent the date when the owner_id was added/found/moved.
I have created a dynamic sql function that can retrieve the owner_ids unique among all of these tables and put them into an owner table - this part works fine and takes appx. 5 min to complete.
However, I am struggling to update the source table's (fiscal_years) process_date field if its owner_id exists in the owner table.
I have tried this:
update fiscal_year y set process_date = now() where exists (select owner_id from owners o where o.owner_id = y.owner_id) and process_date is null;
But predictably, this query ran for 2 hours before I stopped it.
I also tried to use an etl tool to update the fiscal_year table using owners.owner_id as a key. But this method never finished either - presumably because there are many owner_ids that don't exist in each fiscal_year table.
How can I update the fiscal_years table when the owner_id is present in the owners table?
Any help on this would be greatly appreciated.