Results 1 to 4 of 4

Thread: Select Top

  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Select Top

    I need to delete a variable number of records from a table.

    Code:
    DELETE FROM tbMyTable 
    WHERE Field IN 
    (SELECT TOP @iCount Field 
    FROM tbMyTable WHERE Condition = TRUE)
    Is there a way to do this?

    Mike Bujak

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Go out to lunch....get completely bombed...open qa and start typing

    OK, I need to know why variable....

    Damn, I had to ask....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The general solution to these problems is to use dynamic SQL:

    declare @SQLString varchar(500)
    set @SQLString = 'DELETE FROM tbMyTable WHERE Field IN (SELECT TOP ' + @iCount + ' Field FROM tbMyTable WHERE Condition = TRUE)'
    Exec (@SQLString)

    If you are dynamic-averse, then you can accomplish the same thing with a more complex query if you have a unique field to sort on. Be aware that in your example you did not include a sort order for your TOP clause, so while you may be able to specify how many rows to delete, you have no control over WHICH rows to delete. You might want to fix that...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    or rowcount which doesn't require dynamic sql.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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