Non-trivial Merge Of Two Tables

There are two tables 1 and 2 with columns: Id: unique ; timestamp:long; money:double SOME RECORDS are missing in Table 1 and table2. Question: try to merge both tables together w

Solution 1:

You don't say whether you want to merge into one of the existing tables or into a new table. But either way it is not "non-trivial".

If you want to insert the set of data from one of the existing tables into the other, use MERGE (the clue is in the question).

SQL>select*from t1;

        ID TS             MONEY
---------- --------- ----------125-JUL-09123204-AUG-0967SQL>select*from t2;

        ID TS             MONEY
---------- --------- ----------208-AUG-0967310-AUG-09787SQL>mergeinto t1
  2using   t2
  3on ( t1.id = t2.id )
  4when matched then5updateset ts = ts + ((t2.ts - t1.ts) /2)
  6whennot matched then7insert8             (id, ts, money)
  9values10              (t2.id, t2.ts, t2.money)
 11/2rows merged.

SQL>select*from t1
  2/

        ID TS             MONEY
---------- --------- ----------125-JUL-09123210-AUG-0967310-AUG-09787SQL>

If you want to insert both sets of data into a new table then you can do it like this:

SQL> insert all
  2when t1_id = t2_id then
  3into t3 values (t1_id, t1_ts + ((t2_ts - t1_ts)/2), t1_money)
  4      when t1_id isnotnulland t2_id isnull then
  5          into t3 values (t1_id, t1_ts, t1_money)
  6      when t1_id isnulland t2_id isnotnull then
  7          into t3 values (t2_id, t2_ts, t2_money)
  8  select t1.id as t1_id
  9         , t1.ts as t1_ts
 10         , t1.money as t1_money
 11         , t2.id as t2_id
 12         , t2.ts as t2_ts
 13         , t2.money as t2_money
 14  from t1 full outer join t2 on t1.id = t2.id
 15  /
SQL> select * from t3
  2  /

        ID TS             MONEY
---------- --------- ----------
         206-AUG-0967125-JUL-09123310-AUG-09787

SQL>

Solution 2:

What DB are you using as DB:s support different merge commands. Also can you use some stored procedure?