TL_ID VALID_FROM BATCH_ID CURRENT_FLAG SOURCE PUBLISHED_FLAG
10001 01-SEP-15 22.00.13.000000000 100 Y 5 Y
10002 01-SEP-15 22.00.13.000000000 100 Y 5 Y
10003 02-SEP-15 09.00.13.000000000 101 Y 8 Y
10001 29-SEP-15 15.36.02.354673000 129 Y 8 N
10002 01-OCT-15 22.00.02.354673000 131 Y 8 N
10003 01-OCT-15 09.00.13.000000000 131 Y 8 N
10004 02-OCT-15 11.00.02.354673000 132 Y 8 N
I have a table called lookup and it has fixed records from older database which can be identified by SOURCE = '5'. We used to receive new records from daily batch and those records can be identified by SOURCE = '8'.
TL_ID & VALID_FROM are primary key for this table.
My requirement is,
1. Whenever a new record comes it should check the table for any match using TL_ID (For ex, 10001, 10002,10003 in the above case). If it matches it should simply update the following column in existing record
CURRENT_FLAG = 'N'
PUBLISHED_FLAG = 'Y' in the new record.
2. Suppose if no matches found (for ex, TL_ID 10004) then simply update the PUBLISHED_FLAG = 'Y'.
The table might have 1 to 2 million records and chance for growing upto 5m. So I am looking for optimal solution and planning to schedule this function/procedure to run on daily basis.