Vertically Merge Multiple Tables in MySQL by Joint Primary Key
- by world
Hello, I'll attempt to make my question as clear as possible.
I'm fairly unexperienced with SQL, only know the really basic queries. In order to have a better idea I'd been reading the MySQL manual for the past few days, but I couldn't really find a concrete solution and my needs are quite specific.
I've got 3 MySQL MyISAM tables: table1, table2 and table3.
Each table has an ID column (ID, ID2, ID3 respectively), and different data columns.
For example table1 has [ID, Name, Birthday, Status, ...] columns,
table2 has [ID2, Country, Zip, ...],
table3 has [ID3, Source, Phone, ...]
you get the idea.
The ID, ID2, ID3 columns are common to all three tables... if there's an ID value in table1 it will also appear in table2 and table3. The number of rows in these tables is identical, about 10m rows in each table.
What I'd like to do is create a new table that contains (most of) the columns of all three tables and merge them into it.
The dates, for instance, must be converted because right now they're in VARCHAR YYYYMMDD format. Reading the MySQL manual I figured STR_TO_DATE() would do the job, but I don't know how to write the query itself in the first place so I have no idea how to integrate the date conversion.
So basically, after I create the new table (which I do know how to do), how can I merge the three tables into it, integrating into the query the date conversion?
