Insert and update based on condition (without timestamp).
I have encountered a problem when I am trying to write a script to one version of "Insert and Update" Incremental Load.
Example: To simplify the example I have made an illustration of how I want the data-set to update. (I leave the code for the discussion)
In the example above you can see that I both want insert new records and to update records. The condition is that I only want to update records if the new value is greater than the existing record.
For instance, the existing record for ID 2 equals to 0 (Table 1) and since the new record for ID 2 equals to 100 (Table 2) I want to Update that record so the final updated record for ID 2 equals to the highest value of them two (Updated Table). If Table 2 contains a new record I just want to add that record to the final data-set.
1. Insert new records
2. Update records if the value is higher than the existing record
What do you guys think is the best solution for this kind of problem?