CODEWITHSUNDEEP
mysql
FKM
FKM

Reputation: 143

How to combine two SQL table's data to one new SQL table?

I am migrating from Joomla to a React based App for website. I will need to collect data from two tables of the old database to create one table of the new database.

Is there a tool, a program or a way to do this ?

Table 1 In Joomla users are in a separate table. This table (users table) has the following columns:

id

name

username

email (there are a few more but I do not need them)

Table 2 The profile for users was stored in another table on my Joomla site. The columns in the table (profiles table) are:

user_id(same as id from table above)

online (Do not need)

validated (Do not need)

main_photo

status

gender

field_6

field_16

Table 3 = (Table 1 + table 2) I want to combine these two tables to a new MYSQL database. Once I combine, I can just import the database to my new site's SQL table and it will have all the user data from the old site. The columns of the new site table are:

id (same as id from Table 1 and user_id from Table 2)

username (same as username from Table 1)

firstname (same as name from Table 1)

email (same as email from Table 1)

bio (same as status from Table 2)

instagram (new field - not in Table 1 or 2)

twitter (new field - not in Table 1 or 2)

gender (same as gender from table 2)

religion (same as field_6 from table 2)

age (same as field_16 from table 2)

Upvotes: 0

Views: 58

Answers (2)

Nick
Nick

Reputation: 147166

You can do this with a CREATE TABLE ... SELECT query. To get the specific fields you name, you would use this query:

CREATE TABLE table3 AS
SELECT id, username, name AS firstname, email, status AS bio,
    '' AS instagram, '' AS twitter, gender, field_6 AS religion,
    field_16 As age
FROM table1 t1
JOIN table2 t2 ON t2.user_id = t1.id

Note that because you don't have a value for the instagram and twitter fields, you will need to reset their definitions. A SHOW CREATE table3 query shows the problem:

CREATE TABLE `table3` ( `id` int(11) DEFAULT NULL,
    `username` varchar(20) DEFAULT NULL, 
    `firstname` varchar(20) DEFAULT NULL,
    `email` varchar(50) DEFAULT NULL, 
    `bio` varchar(100) DEFAULT NULL,
    `instagram` char(0) NOT NULL DEFAULT '',
    `twitter` char(0) NOT NULL DEFAULT '',
    `gender` varchar(1) DEFAULT NULL,
    `religion` varchar(20) DEFAULT NULL,
    `age` int(11) DEFAULT NULL )
    ENGINE=InnoDB DEFAULT CHARSET=utf8

You will need an ALTER TABLE command to set an appropriate definition for those fields e.g.

ALTER TABLE table3 CHANGE instagram instagram VARCHAR(50), 
                   CHANGE twitter twitter VARCHAR(50);

Note the column definitions are just examples from a test case I made. Yours will copy from the existing tables.

Upvotes: 1

Rinsad Ahmed
Rinsad Ahmed

Reputation: 1933

CREATE TABLE Table3 AS 
(SELECT Table1.*, 
      Table2.*
 FROM   Table1
      INNER JOIN Table2
              ON Table1.id = Table2.user_id);

Upvotes: 1

Related Questions