CODEWITHSUNDEEP
mysql
Mahesh Mahesh.G
Mahesh Mahesh.G

Reputation: 87

How to merge records from two tables into third using MYSQL

I have 3 tables A, B, C

Schema of all 3 tables is same as mentioned below:

1st A table:

cpid ,name, place

2nd B table:

connectorid,dob

3rd C table:

ccpid cconnectorid

Now both tables A and B have many records. Now some of the records in A and B are with same id. Now I want to merge the records from A and B into Table C. Merge logic is as follows

1)If records with cpid = connectorid ,insert into table c.
2)C Table  ccpid is the foreignkey for A table cpid and cconnectorid is the foreignkey B table connectorid.
3)Using select query.

Upvotes: 3

Views: 111

Answers (4)

Hrabosch
Hrabosch

Reputation: 1583

You just need join data from both tables? This is simple JOIN function.

SELECT *
    FROM Table_A
    INNER JOIN Table_B
    ON Table_A.cpid =Table_B.connectorid;

You can insert this select to your Table_C.

Here is INNER JOIN, but I think you should take a look to JOINs, here are examples and you can read more about other JOINs.

INNER JOIN: Returns all rows when there is at least one match in BOTH tables LEFT JOIN: Return all rows from the left table, and the matched rows from the right table RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table FULL JOIN: Return all rows when there is a match in ONE of the tables

Upvotes: 1

NikuNj Rathod
NikuNj Rathod

Reputation: 1658

You can try this solution for your query:

INSERT INTO `C`(`ccpid`, `cconnectorid`, `ccity`)
SELECT ta.`cpid`, ta.`cconnectorid`, tb.`place`
FROM `A`  as ta
  INNER JOIN `B` tb ON ta.`cpid` = tb.`cconnectorid`

Upvotes: 1

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2988

use following query replace with your table names 

INSERT INTO CTABLE(ccpid,cconnectorid,ccity)
(SELECT A.cpid ,B.connectorid, A.place FROM 
TABLEA A INNER JOIN TABLEB B ON A.cpid = B.connectorid)

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You can use select insert with a n inner join 

 insert into table_c
 select a.cpid, b.connectorid, a.place
 from table_b as b
 inner join table_a as a  on a.id = b.id

Upvotes: 1

Related Questions