How to merge two SQL tables without duplicates of a single column?

user_5441525 0
Viewed 242

How do I import all values from a different SQL Table only importing lines that have a non duplicate 'b' entry. Because 'b' symbols a unique string in my tables. Both tables have ca. 5mio entry's.

A_Table
structure
'id', 'a', 'b', 'c', 'd'

B_Table
structure
'a', 'b', 'c'

Add to A_Table from B_Table where 'A_Table.b' !found in 'B_Table.b'

Example:

A_Table

    1, "a1", "Hallo1", "c", "d"
    2, "a1", "Hallo2", "c", "d"
    3, "a1", "Hallo3", "c", "d"
    4, "a1", "Hallo5", "c", "d"
    5, "a1", "Hallo7", "c", "d"
    6, "a1", "Hallo8", "c", "d"

B_Table

    "a2", "Hallo1", "c"
    "a2", "Hallo2", "c"
    "a2", "Hallo3", "c"
    "a2", "Hallo4", "c"
    "a2", "Hallo5", "c"
    "a2", "Hallo6", "c"
    "a2", "Hallo9", "c"

A_Table after query

    1, "a1", "Hallo1", "c", "d"
    2, "a1", "Hallo2", "c", "d"
    3, "a1", "Hallo3", "c", "d"
    4, "a1", "Hallo5", "c", "d"
    5, "a1", "Hallo7", "c", "d"
    6, "a1", "Hallo8", "c", "d"
    7, "a2", "Hallo4", "c", ""
    8, "a2", "Hallo6", "c", ""
    9, "a2", "Hallo9", "c", ""
mysql sql
2 Answers

I think you want union all:

insert into a_table (a, b, c, d)
    select a, b, c, ''
    from b_table b 
    where not exists (select 1 from a_table a where a.b = b.b);

If you care about performance, you want an index on a_table(b).

This assumes that id is assigned automatically.

If you just want a result set, then union all:

select id, a, b, c, d
from b_table a
union all
select a.max_id + row_number() over (order by b.a, b.b, b.c),
       b.a, b.b, b.c, ''
from b_table b cross join
     (select max(id) as max_id from table_a) a
where not exists (select 1 from a_table a where a.b = b.b);
user_11440350

Try this:

insert into A_Table (a,b,c)
(select B_Table.a, B_Table.b, B_Table.c from B_Table left join A_Table on 
 A_Table.b=B_Table.b where A_Table.id is null)
user_56940680
Related