asked on

MySQL - search for duplicate records across all tables in a database

I have multiple tables in a database. Each table contains race results for a given year.

The tables contain 10 columns including surname VARCHAR), dob (VARCHAR) and pin3 (INT)

I need a query that will do the following:-

Find records (matched on surname and dob) that appear 3 times or more across all of the tables AND where pin3 does not equal "1"

HainKurt

try

select surname, dob, pin3
from (
select surname, dob, pin3 from table1 where pin3 <> 1
union all
select surname, dob, pin3 from table2 where pin3 <> 1
union all
...
union all
select surname, dob, pin3 from table10 where pin3 <> 1
) x
group by surname, dob, pin3
having count(1) >= 3

Open in new window

Chris Millard

ASKER

Is there any better way of doing this by enumerating the tables first rather than selecting from every table? It's manageable right now doing that, but probably won't be in the future.

HainKurt

maybe you can create a temp table on master db as

dups (surname, dob, pin3, cnt default=0) PK=surname, dob, pin3

Open in new window

then

truncate table dups;
insert into dups (select surname, dob, pin3, 0 from table1 where pin3 <> 1);

update d set cnt=cnt+1
  from dups d inner join table2 t on d.surname=t.surname and d.dob=t.dob and d.pin3=t.pin3 and t.pin3 <> 1;

update d set cnt=cnt+1
  from dups d inner join table3 t on d.surname=t.surname and d.dob=t.dob and d.pin3=t.pin3 and t.pin3 <> 1;

...

update d set cnt=cnt+1
  from dups d inner join table10 t on d.surname=t.surname and d.dob=t.dob and d.pin3=t.pin3 and t.pin3 <> 1;

select * from dups where cnt>=3;

Open in new window

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.