CODEWITHSUNDEEP
mysqlmergeunion
Diederik Slob
Diederik Slob

Reputation: 57

Mysql: Merging two similar tables with overlapping unique columns

I have compiled two tables with plant names from different sources (A and B), which I want to merge into a third table (C). Table A and B both use the species name as a unique value, and contain Dutch names as well. Like this:

SPECIES           | DUTCH NAME
Ligustrum vulgare | Wilde liguster
Linaria vulgaris  | Vlasbekje
Linnaea borealis  | Linnaeusklokje

I want to merge these tables, but they have a lot of species in common. I want to give A priority over B, and only add to A if B has a species that A lacks. However, if a species in A doesn't have a Dutch name, than I want to add the Dutch name from B, if it's available. The species column in the new table is also set to unique.

I bet this needs to be done with UNION, and I did look around for examples, but I guess I am a bit too brain-fried to get it. What should I do? Thanks in advance!

Upvotes: 0

Views: 53

Answers (1)

Akina
Akina

Reputation: 42632

INSERT INTO c (species, dutch)
SELECT species, COALESCE(a.dutch, b.dutch)
FROM ( SELECT species FROM a
       UNION 
       SELECT species FROM b ) AS all_species
LEFT JOIN a USING (species)
LEFT JOIN b USING (species);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7f2dea9f56071c4dc0dd9abc93360624

Upvotes: 2

Related Questions