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
)
)
精彩评论