SQL merging two big data tables without a unique key

Question

I have the following tables that need to be merged:

table 1:

main_key | sec_key | old_number | var1 | var2

 1      |  A    |    5     | AA | 11
 1      |  B    |    8     | BB | 22

table 2:

main_key | new_number | var1 | var2

 1      |   2       | DD | 44
 1      |   3       | EE | 55
 1      |   7       | FF | 66

I cannot modify the tables (no inserts hence I cannot use 'merge', only UNION).

Both tables contain a lot of data and I need the most efficient way to merge them.

The merging should be according to the main_key and, for each of the table1.sec_key,
if the table2.new_number < table1.old_number, then update the values of table1.var1, table1.var2 to the corresponding values: table2.var1 and table2.var2.

Also, there should be a line of the original table1 values with the old_number.

Example:

For the first table1.sec_key: its old_number is 5.
table2.new_number '2' is smaller than 5, also table2.new_number '3' is smaller than 5 but table2.new_number '7' is bigger than 5
so the output will include the old_numbers and the lines for new_number=2 and new_number=3:

(The same goes for the rest of the sec_keys and for all other sec_keys in other main_keys.

Expected output_table for the example:

main_key | sec_key | number | var1 | var2
    1    |    A    |    5   |  AA  |  11
    1    |    A    |    2   |  DD  |  44     
    1    |    A    |    3   |  EE  |  55
    1    |    B    |    8   |  BB  |  22  
    1    |    B    |    2   |  DD  |  44     
    1    |    B    |    3   |  EE  |  55
    1    |    B    |    7   |  FF  |  66

I thought of either using UNION or join, but not sure how to do this and keep the original line from table1 for each sec_key.

My problem is that the main_key is not a unique key. I've also considered using CTE but not sure it's useful here.

How come B comes for 4, while A only 3 times in expected out put? — Sashi Kant
because the old_number associated with B (in table1) is bigger than all 3 new_numbers in table2, but the old_number of A is only bigger than 2 new_numbers in table2 — Lil Shaniv

Jonathan Leffler Jonathan Leffler · Accepted Answer · 2012-12-11T01:58:54

It seems to me that you need a UNION of the data from table1 plus a specialized join of table1 with table2:

SELECT main_key, sec_key, old_number AS number, var1, var2
  FROM table1
UNION
SELECT t1.main_key, t1.sec_key, t2.new_number AS number, t2.var1, t2.var2
  FROM table1 AS t1
  JOIN table2 AS t2 ON t2.main_key = t1.main_key AND t2.new_number < t1.old_number

On your test data, it produces the answer you want (with ordering controlled by ORDER BY main_key, sec_key, number):

1   A   2   DD   44
1   A   3   EE   55
1   A   5   AA   11
1   B   2   DD   44
1   B   3   EE   55
1   B   7   FF   66
1   B   8   BB   22

If you really want the original rows to appear before the merged rows, you have to do a little more work:

SELECT u.main_key, u.sec_key, u.number, u.var1, u.var2
  FROM (SELECT 0 AS pseudo_order, main_key, sec_key, old_number AS number, var1, var2
          FROM table1
        UNION
        SELECT 1 AS pseudo_order, t1.main_key, t1.sec_key, t2.new_number AS number,
               t2.var1, t2.var2
          FROM table1 AS t1
          JOIN table2 AS t2 ON t2.main_key = t1.main_key AND t2.new_number < t1.old_number
       ) AS u
 ORDER BY u.pseudo_order, u.main_key, u.sec_key, u.number;

Output:

1   A   5   AA   11
1   A   2   DD   44
1   A   3   EE   55
1   B   8   BB   22
1   B   2   DD   44
1   B   3   EE   55
1   B   7   FF   66

SQL merging two big data tables without a unique key

1 Answers