Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    212

    Unanswered: Simple Delete SQL

    I want to delete top 100 records in a table where a certain condition is true
    My sql looks like:

    Delete TOP 100 * From db..tablename Where cond1 = @var1 AND Convert(Char(8), Date_Time, 112) = @var2

    In Query Analyzer this gives me an error at 'TOP', why?

    Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    TOP is not a valid option with the DELETE statment.
    Last edited by Paul Young; 10-21-03 at 09:53.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Sep 2003
    Posts
    212
    Originally posted by Paul Young
    TOP is not a valid option with the DELETE statment
    So how would I delete the top 100 records in a table?

    Any ideas?

    Thanks

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    You might try somthing like:

    Code:
    Delete tn
      from db..tablename tn
      join (select TOP 100 <key field(s)> 
              From db..tablename 
             Where cond1 = @var1 
               AND Convert(Char(8), Date_Time, 112) = @var2) as t1 on tn.<key field(s)> = t1.<key field(s)>
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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