Creating one table with Unique rows from two tables

  • 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.

  • 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?


    Kindest Regards,

  • 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

  • 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

                               

     

     

     

     

     

     

     

     

  • 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).

  • 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