Results 1 to 4 of 4

Thread: SQL Statement

  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Statement

    I am trying to write a sql statement to delete some rows from a table. I have the code to go through the entire table, but I am only wanting to go through the first 100 rows of the table. I was thinking I need to do a for next loop of something like that, however I dont know how to do it in SQL.
    Any help is appreciated.
    Thanks

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement

    From SQL BOL (Books Online):

    Use DELETE and a SELECT with the TOP Clause
    Because a SELECT statement can be specified in a DELETE statement, the TOP clause can also be used within the SELECT statement. For example, this example deletes the top 10 authors from the authors table.

    DELETE authors
    FROM (SELECT TOP 10 * FROM authors) AS t1
    WHERE authors.au_id = t1.au_id

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement

    Here is my code:
    DELETE [downtime data]
    FROM (SELECT TOP 100 * FROM [downtime data]
    WHERE (
    ([Tagname] <> 'S161_Pull_Down_Final') AND
    ([Tagname] <> 'S162_Pull_Down_Final') AND
    ([Tagname] <> 'S163_Pull_Down_Final') AND
    ([Tagname] <> 'N161_Pull_Down_Final') AND
    ([Tagname] <> 'N162_Pull_Down_Final') AND
    ([Tagname] <> 'N163_Pull_Down_Final'))

    I get a syntax error at before WHERE. If I add the ast1 in the code, it says Tagname is invalid when I run it.
    Thanks

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Statement

    A couple of syntax points. You don't need parens around each where item, nor around the where clause. Although, there should not be any problem with keeping them.

    The other point is that you need to join the two instance of the table. The table referenced in the DELETE statement does not currently know how to join to the table in the SELECT subquery.

    Try something like:<pre>DELETE [downtime data]
    FROM (SELECT TOP 100 * FROM [downtime data]
    WHERE [Tagname] NOT IN ('S161_Pull_Down_Final', '.....')
    ) d
    WHERE [downtime data].YourKeyField = d.YourKeyField</pre>

    (Notice, I used "NOT IN" in the WHERE clause rather than the ANDs. Simply add each item and separate with commas.)
    You'll need to change the "YourKeyField" name to the name of your key field.

Posting Permissions

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