Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: DELETE FROM tableA WHERE id not in @includeList

    for MS SQL 2000
    how can I do

    DECLARE @IncludeList TABLE (id int PRIMARY KEY clustered)
    INSERT INTO @IncludeList SELECT tbX.id FROM tbX

    DELETE FROM tbA WHERE tbA.id NOT IN (@IncludeList.id)
    DELETE FROM tbB WHERE tbB.id NOT IN (@IncludeList.id)
    DELETE FROM tbC WHERE tbC.id NOT IN (@IncludeList.id)


    I want to get an id list into a variable or a temp table to delete faster on many table

    I cannot use on delete cascade with foreign key because i need sometimes keeping the rows

    thank you for helping

  2. #2
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    delete from tbla where tbla.id not in (select id from @IncludeList)

    or

    delete from tbla where tbla.id not exists (select * from @IncludeList x where x.id = tbla.id)

    or

    delete a
    from tbla a inner join @IncludeList l
    on a.id = l.id
    -----------------
    KH


  3. #3
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    The last one should be

    delete a
    from tbla a left outer join @IncludeList l
    on (a.id = l.id)
    where l.id is null;

    right? The code you posted was an "where exists"
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  4. #4
    Join Date
    Dec 2005
    Posts
    266
    it works fine with

    delete from tbla where tbla.id not in (select id from @IncludeList)

    but which one of your 3 methods is the fastest ?

    thanks a lot

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why bother with creating @IncludeList table? why not just use tbX? that would be faster
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    check the query plans for the most efficient one. I would favor a left join directly to tbX.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •