subtracting one table from another

  • HI,

     

    I have a large sql table and two smaller ones. The two smaller ones are derived from the big one. Say table A is the big one, I wish to removed all the rows from table A that are contained in B and C.

     

    I have been trying this:  select * from tableA where exists(select * from TableB)

     

    All this does is give me everything from tableA. Say table B was 2000 rows , I would like to see TableA with 2000 records less.

     

    Any help would be appreciated,

     

    thanks,

     

    Paul

  • Look up EXISTS() in BOL. U'll see where u r wrong.

  • So you actually want to DELETE the data from table A that's already in tableb?

    If so

    delete from TableA

    where tableA.ID in (select ID from tableB)

    should do the trick.  Obviously, I'm assuming that ID is a unique key on both tables.

    Or to select from table A what's not in table B:

    select * from tableA

    where tableA.id not in (select id from tableB)

    Regards

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Phil's reply looks good.  I'd advise setting up some sample data in test tables before trying it on the live tables.

  • to answer your original question.

    Select *

    from tablebA

    where exists (select *

                  from tableb

                  Where Tablea.id = tableb.id)

    You need a where clause indicating what criteria you need to establish what data is in both tables, I'm assuming an id column of some sort.

  • This would also do the same

     

    Delete from A

    Where KeyColumn  In

    (

    Select KeyColumn from B

    Union

    Select KeyColumn from C

    )

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply