CODEWITHSUNDEEP
mysql
divided
divided

Reputation: 1319

MySQL: Get list of duplicate values between tables

I have two tables that have address data. Here is a partial representation:

table 1 (main data):

id  address        city      state
1   1234 main st   seattle   wa
2   2345 oak st    portland  or
3   3456 maple st  boise     id

and table 2 (secondary data):

id  address         city         state    to_insert
1   4567 first st   sacramento   ca       1
2   5678 second st  phoenix      az       0
3   2345 oak st     portland     or       1

I need to first get a list of values from table 2 that are flagged as to_insert (bit) that already exist in table 1, so I can manually edit the data and insert them (so they're not duplicate). How do I get a list of duplicates from table 2 (secondary data) that already exist in table 1 (main data)?

Upvotes: 0

Views: 46

Answers (4)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use an insert select with not in subselect

insert into table1 (address , city , state)
select address, city,  state
from table2 
where  (address , city , state) not  in (select address, city,  state from table1)

Upvotes: 1

denny
denny

Reputation: 2254

if you want those values which are not duplicate

insert into  table1 select * from table2 where 
row(id,address,city,state) not in (select * from table1);

if you want those values which are duplicate

insert into table1 select * from table2 where 
row(id,address,city,state) in (select * from table1);

Upvotes: 1

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You can use EXISTS to get the duplicate rows on table2:

SELECT * FROM table2 t2 WHERE EXISTS (
    SELECT * FROM table1
    WHERE t2.address = table1.address
        AND t2.city = table1.city
        AND t2.state = table1.state
);

You can find a working example here: http://sqlfiddle.com/#!9/110b9a/1/0

To INSERT the data from table2 to table1 you can use the following:

INSERT INTO table1 (address, city, state) 
SELECT address, city, state FROM table2 
WHERE NOT id IN (
    SELECT t2.id FROM table2 t2 WHERE EXISTS (
        SELECT * FROM table1
        WHERE t2.address = table1.address
            AND t2.city = table1.city
            AND t2.state = table1.state
    )
);

Upvotes: 1

user7382013
user7382013

Reputation: 21

Do a SELECT with a GROUP BY clause. Let's say name is the column you want to find duplicates in:

SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;

This will return a result with the name value in the first column, and a count of how many times that value appears in the second.

Upvotes: 0

Related Questions