I don't understand your relationships. In the "lookup" table, you have this:
How does master_id in this table correspond to master_id in the original table?
master_id merged_id merge_request
B A 01/01/2010
C B 02/01/2010
D C 03/01/2010
Possible interpretations/combinations/lookups could be:
1) master_id on original table matches with master_id on lookup table and merged_id on lookup table becomes new merged_id in output table
2) merged_id on lookup table matches with master_id on original table and master_id on lookup table becomes the new master_id in output table
3) merged_id on lookup table matches with merged_id on original table and master_id on lookup table becomes new master_id in output table
There are undoubtedly more possibilities. Without any idea of how the lookup table or the original table will be used or what the merge_request date field means or what type of subsequent processing or output report you hopw to see, it is hard to make any recommendations.
To me, in the month of January, it lookes like A was merged with B. Then in Feb, it looks like B was merged with C, but does that mean that A was also merged with C, but only for Feb?? Then in March, C was merged with D, does that mean that A and B were also merged into D -- but only for March??? Your lookup table does not distinguish time periods. It looks like you are possibly rolling up all merges to the most recent master_id??? But wouldn't D be the wrong master_id for A in the month of January????
Having some idea of the whole process and how you are using these tables would come in handy.