Problem in finding duplicates between 2 tables

sgiri · New User Joined: 07 Aug 2007 Posts: 20 Location: chennai

Hi all ,
I have problem in finding duplicates between 2 tables. Let the 2 tables be A - ( active ) & B - (Archive). A - has 6 columns and 3 indexes . While B has 7 columns and 4 indexes.

Can any one suggest me some query to find out such duplicates between A and B?

NOTE : A DUPLICATE IN OUR CONTEXT MEANS A SAME ROW EXISTING IN BOTH 'A' & 'B'.

Prajesh_v_p · Active User Joined: 24 May 2006 Posts: 133 Location: India

Why cant you just try a select with join between these tables A and B on columns you want to find out duplicates?

stodolas · Posted: Mon Aug 13, 2007 6:28 pm

Well, none of the rows will be the same. Table B has an extra column. Do the tables have identical primary keys?

dick scherrer · Posted: Mon Aug 13, 2007 9:20 pm

Hello,

It may help if you provide a better definition of "duplicate".

It may also be easier and maybe even faster to unload the tables and do your comparison in batch against the sequential files.

stodolas · Posted: Mon Aug 13, 2007 9:38 pm

It also depends on what you plan on doing with those duplicates. Are you wanting to delete duplicates, process something based on that 7th column, or just identify them?

Simply identifying them is fairly simple with some SQL. Be sure to match the WHERE so the columns of the primary key are the first matched, then work through the indexes and end with those columns that aren't part of any index.

sgiri · New User Joined: 07 Aug 2007 Posts: 20 Location: chennai

Thank you stoldas what you have said worked out ... Thank you !!