July 20, 2004 at 2:32 pm
I have been assigned a project where i must compare two tables of account information. Table one is our current account Database, Table two is a new account database. What I need to do is find out which accounts in Table two are not found in table one, in other words, i need to query which are the unique accounts between the two tables, i need all of the accounts found in Table two that are not found in table one. We would basically like to kick out all of the accounts in table two that also appear in table one so all accounts in table two are unique. Hope this makes sense.
July 20, 2004 at 5:43 pm
The first query you need to perform is;
SELECT * FROM Table2 WHERE PrimaryKeyID NOT IN (SELECT PrimaryKeyID FROM Table1)
This will give you the records that are not in Table1 but are in Table2.
Your last sentence confuses me compared to your first requirement!
If you initially want to find out which records exist in Table2 that are NOT IN Table1, why would you want to delete records in Table2 that do exist in Table1?
July 20, 2004 at 8:53 pm
Sounds like MrSQL gave you the first half and your 2nd half you need to
DELETE FROM table2 WHERE AccountID IN
(SELECT T2.AccountID
FROM table2 T2
INNER JOIN table1 T1 ON T2.AccountID = T1.AccountID)
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 21, 2004 at 1:20 am
1. i need to query which are the unique accounts between the two tables
select * from TableOne
union
select * from TableTwo
2. i need all of the accounts found in Table two that are not found in table one
select * from Tabletwo as two
where not exists (select primarycolumn from Tableone as one
where one.primarycolumn = two.primarycolumn)
Hope this will be ok.
Thanks,
Ganesh
July 21, 2004 at 9:05 am
I just wanted to clarify something with the UNION statement...
As Ganesh has correctly pointed out, using a UNION will automatically remove any duplicate records. Additionally, if the need ever arises, you will use UNION ALL to include all records from both tables (including any duplicate records).
July 21, 2004 at 10:49 am
I'd try to avoid the subselect if possible. While they are fine for a quick and dirty query, they are a slower than using joins for the same purpose. Probably not an issue for your one time query, but it could make a difference if the tables are really big. How about:
select table2.*
from table2 left outer join table1 on (table2.PrimaryKey = table1.PrimaryKey)
where table1.PrimaryKey is null
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply