Insert and update based on condition (without timestamp).

[Anonymous]

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.

Description:

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?

Best regards,

Olov

[Anonymous]

Refer this post Incremental Load.

[Anonymous]

I understand the idea with incremental load but this problem does not have a modification-date as described in the refered post.

[marcus_sommer · Accepted solution]

You could try something like this:

t1:

load * inline [

ID, Value

1,500

2,0

3,100

4,300

5,0

];

t2:

noconcatenate load * inline [

ID, Value

2,100

3,700

4,300

6,500

7,0

];

t3:

noconcatenate load * resident t1;

    join(t3)

load ID, Value as ValueN resident t2;

t4:

noconcatenate load ID, rangemax(Value, ValueN) as Value resident t3;

drop tables t1, t2, t3;

- Marcus

[Anonymous]

Thank you Marcus!

I really appreciate that you took some time helping me out.

-Olov