CODEWITHSUNDEEP
phpmysql
Bluemagica
Bluemagica

Reputation: 5158

mysql query to select data from table2 which does not exist in table1

I have 2 tables table1 and table2, where table1 contains the data collected so far, and table2 contains table1's data along with some updated/additional data. I need to retrieve only this updated/additional data and insert it into table1.

Now I know I can use NOT IN to do this, but I am not sure it will be a very efficient solution in case of a huge number of records. Any suggestion on what the best approach would be in terms of execution speed?

Upvotes: 0

Views: 233

Answers (2)

John Woo
John Woo

Reputation: 263893

I'm not sure if i've understand your question correctly but let me give it a try. Suppose you have a design like this:

TableA : {colA, colB, colC, colD, colE}
TableB : {colA, colB, RecC, RecD, RecE}

where Tables (tableA, tableB) is joined on ColA. I Assumed that TableA's columns (colC, ColD, colE) will be updated and the records are based on TableB's columns (recC, recD, recE).

In your statement: I need to retrieve only this updated/additional data and insert it into table1.. I think you want to update TableA's records based on TableB

UPDATE TableA a INNER JOIN TableB b ON a.ColA = b.ColA
SET    a.ColC = b.RecC,
       a.ColD = b.RecD,
       a.ColE = b.RecE
-- WHERE (Condition)          -- if you want to have a condition.

so the statement above updates all the records in tableA if colA also exist in tableB since I've used INNER JOIN. You could also use LEFT JOIN.

Upvotes: 0

user319198
user319198

Reputation:

This can be done with simple join both tables

something like below:

select t1.* from table1 as t1 join table2 as t2 on t1.id=t2.id where ...[]

Upvotes: 3

Related Questions