how to join 2 tables into one with SQL

Question

I have 2 tables, Let's name them tb1 and tb2.

I want to add all items in tb1 that dose not exist in tb2 into new rows in tb2. At the same time I want to update existing data in tb2 with the data in tb1, I try to understand join, merge and so on but I could not understand how doing that in SQL.

For the question I will build this 2 tables and the result I try to achieve.

tb1:

| KEY  | col one    | col two 
+------+------------+-----------
| 1    | data one   | data one 
| 2    | data two   | change data
| 3    | data three | data three

tb2:

| KEY  | col one   | col two 
+------+-----------+-----------
|  1   | data one  | data one
|  2   | data two  | old data
|  4   | data four | some data

tb2 after SQL :

We can see we add the key 3 and we change in key 2, col 2 data

| KEY  | col one    | col two 
+------+------------+-----------
|  1   | data one   | data one
|  2   | data two   | change data
|  3   | data three | data three
|  4   | data four  | some data

Just to clarify your requirements: (1) Combine data from both tables (2) Remove duplicates (3) Where values differ for the same key in the 2 tables, use the values from tb1. Is that correct? — NickW, Dec 09 '20 at 11:31
I dont want to remove duplicate from tb2 i just want to update the data, but we can also remove duplicate and write it again as new data — lidorag, Dec 09 '20 at 12:12
Does this answer your question? [How can I merge two MySQL tables?](https://stackoverflow.com/questions/725556/how-can-i-merge-two-mysql-tables) — Davide Casiraghi, Dec 09 '20 at 14:22

score 0 · Accepted Answer · answered Dec 09 '20 at 12:39

You can generate the results you want using union all:

select t1.key, t1.col1, t1.col2
from table1 t1
union all
select t2.key, t2.col1, t2.col2
from table2 t2
where not exists (select 1 from table1 t1 where t1.key = t2.key);

Actually changing table2 is more cumbersome -- and depends on the database you are using. One method is an insert and update:

update table2
    set col1 = (select t1.col1 from table1 t1 where t1.key = t2.key),
        col2 = (select t1.col2 from table1 t1 where t1.key = t2.key)
    where col1 <> (select t1.col1 from table1 t1 where t1.key = t2.key) or
          col2 <> (select t1.col2 from table1 t1 where t1.key = t2.key);

insert into table2 (key, col1, col2)
    select t1.key, t2.key, t3.key
    from table1 t1
    where not exists (select 1 from table2 t2 where t2.key = t1.key);

Specific databases have many methods for simplifying this logic, including on conflict, on duplicate key update, and merge commands.

how to join 2 tables into one with SQL

1 Answers1