Merge tables by inserting null values for columns where no value can be derived from the merging tables?

I have two different tables and i decided to merge them into one table...

the birth table has

id, name, country, birthday_date, description, link

the death table has

id, name, country, death_date, description, link

and i want to merge them into a single table with the structure

id, name, country, bdate, ddate, description, link.

The link from each table has a unique value so i have to merge the tables using the link. I tried many queries but resulted in wrong results.

Both the birth and death table can have same names and some names may have present in either only birth or d开发者_JAVA技巧eath table.

If then, How can i merge them and updating a null date for a column that has no value on any of the two old tables?

---

Assuming that your new id has AUTO_INCREMENT this solution using CROSS JOIN and COALESCE should work for you. name, country and description from death will be ignored, if available in birth.

INSERT INTO new_table ( name, country, bdate, ddate, description, link )
(
  SELECT
    COALESCE(b.name, d.name),
    COALESCE(b.country, d.country),
    b.birthday_date,
    d.death_date,
    COALESCE(b.description, d.description),
    COALESCE(b.link, d.link)
  FROM birth b
  CROSS JOIN death d ON ( d.link = b.link )
)

---

Alternatively, insert all rows from birth:

INSERT INTO new_table ( name, country, bdate, ddate, description, link )
(
  SELECT name, country, birthday_date, NULL, description, link
  FROM birth
)

Then update those rows with a `death_date´ if available:

UPDATE new_table nt
SET ddate = (SELECT death_date FROM death d WHERE d.link = nt.link )

Finally, add all rows from death, that have no entry in birth:

INSERT INTO new_table ( name, country, bdate, ddate, description, link )
(
  SELECT name, country, NULL, death_date, description, link
  FROM death d
  WHERE NOT EXISTS ( SELECT NULL
                     FROM new_table
                     WHERE link = d.link
                   )
)