Mysql – Combining Tables in MySQL

MySQL

I have two tables, which are very similar, in MySQL. I want to combine them into a single table with new column names. Some of the column names in Table1 or Table2 may not be used in the new combined table. The data types for the similar columns (the ones which will be combined) are the same. I also want to be sure to capture any duplicates from both tables. Each table has it's own auto-incremented primary key.

Best Answer

Yes, it is possible to do. There is a CREATE TABLE ... SELECT clause in MySQL that is designed specifically for that. I recommend you start with a simple SELECT statement that clearly defines what the new table would contain. E.g. if you have a mammals table:

CREATE TABLE mammals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO mammals (name) VALUES
    ('dog'),('cat'),('whale');

and a birds table

CREATE TABLE birds (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO birds (name) VALUES
    ('chicken'),('duck');

You can build this query:

SELECT name FROM mammals
UNION ALL 
SELECT name FROM birds;

The result will contain all animals. However, you may need to keep the animal type in the new table. Modify the query like this:

SELECT name, 1 as type FROM mammals
UNION ALL 
SELECT name, 2 as type FROM birds;

and create a new table

CREATE TABLE animaltype (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     type CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animaltype (type) VALUES
    ('mammal'),('bird');

Now, when you are completely happy with the result execute:

CREATE TABLE animals AS
SELECT name, 1 as type FROM mammals
UNION ALL 
SELECT name, 2 as type FROM birds;

ALTER TABLE animals ADD COLUMN id INT AUTO_INCREMENT NOT NULL PRIMARY KEY;

Optionally:

DROP TABLE mammals;
DROP TABLE birds;

You can also create a foreign key for the animaltype.type column

Related Question