View previous topic :: View next topic
|
Author |
Message |
sgiri Warnings : 1 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'. |
|
Back to top |
|
|
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? |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
Well, none of the rows will be the same. Table B has an extra column. Do the tables have identical primary keys? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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. |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
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.
Code: |
Select A.C1,A.C2,A.C3,A.C4,A.C5,A.C6, B.C1,B.C2,B.C3,B.C4,B.C5,B.C6,B.C7
From A, B
WHERE A.C1 = B.C1, A.C2=B.C2, etc
|
|
|
Back to top |
|
|
sgiri Warnings : 1 New User
Joined: 07 Aug 2007 Posts: 20 Location: chennai
|
|
|
|
Thank you stoldas what you have said worked out ... Thank you !! |
|
Back to top |
|
|
|