SQL Server Performance

delete where a value does not exist in another table...

Discussion in 'SQL Server 2005 General DBA Questions' started by bostondevin, Oct 14, 2008.

  1. bostondevin New Member

    I have two tables (TableA, and TableB) and i'm looking to delete any records in TableA where the values of col1 or col2 do not exist as an id in TableB:
    TableA
    id (auto) col1(int) col2(int)

    1 100 102
    2 101 102
    3 102 103
    4 103 104
    TableB
    id
    100
    102
    103
    105
    So after the query, I want TableA look like this:
    id (auto) col1(int) col2(int)
    1 100 102
    3 102 103
    Any ideas how to write this query?
    Thanks in advance!
  2. Adriaan New Member

    Your description almost spells it out - NOT EXISTS - look it up in Books Online.
  3. danny123 New Member

    select *
    from TableA
    where col1 in (Select ID from TableB) and col2 in (Select ID from TableB
  4. Adriaan New Member

    danny123 - please read more carefully. Your solution should be:
    WHERE col2 NOT IN (select ..............)
    etc.
  5. danny123 New Member

    Sorry my bad.

    Delete
    From TableA
    Where col1 not in (Select ID from TableB) or Col2 not in (select ID from TableB)

    Or other way to do it
    Delete
    From TableA
    Where ID not in (select ID
    From TableA
    where col1 in (Select ID from TableB) and col2 in (Select ID from TableB))
  6. Adriaan New Member

    Now your logic is slightly off ... The criteria is that neither value exists, so in your first query, the criteria must have AND instead of OR.
  7. danny123 New Member

    Correct me if i am wrong...We want values in both columns to be in TableB.
    So as soon as value in Col1 is found not to be in TableB we dont need to go to check for Col2.
    And same with Col2 even if value in Col1 is in TableB, if we find the Col2 value is not in there we donna reject it. So OR should be fine.
  8. Adriaan New Member

    My apologies to danny123, I wasn't reading too carefully myself - row may be deleted if either col1 or col2 does not exist.
  9. Madhivanan Moderator

    As SQL Server 2005 supports deleting a derived table, if it allows union/union all for the same table, it can be possibledelete
    t from
    (
    select col1 as id from TableA
    union all
    select col2 from TableA
    ) as t where not exists (select id from TableB where id=t.id)

Share This Page