dcsimg
Results 1 to 4 of 4

Thread: Simple Delete SQL

  1. #1
    Join Date
    Sep 2003
    Posts
    30

    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
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    use "set rocount" instead

    example:

    use tempdb

    create table temp1 (id int, name char(10))
    insert into temp1 select 1,'a'
    insert into temp1 select 2,'b'
    insert into temp1 select 3,'c'
    insert into temp1 select 4,'d'
    insert into temp1 select 5,'e'
    insert into temp1 select 6,'f'
    insert into temp1 select 7,'g'
    insert into temp1 select 8,'a1'
    insert into temp1 select 9,'a2'
    insert into temp1 select 0,'a3'


    select * from temp1 where id>=5

    set rowcount 2

    delete from temp1 where id>=5

    set rowcount 0

    select * from temp1

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    Or you could just do this:


    Your statement:

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


    The correct statement:

    Delete db..tablename
    From (Select top 100 * From db.tablename Where cond1 = @var1
    AND Convert(Char(8), Date_Time, 112) = @var2) AS temptable
    Where db.tablename.primarykey = temptable.primarykey


    While you can't have the TOP statement in a Delete statement, you can have a select statement in a delete clause and in turn have the TOP statement in the select statement that is in the Delete statement.


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

  4. #4
    Join Date
    Jul 2009
    Posts
    1
    How can I set number of TOP like parameter in procedure e.g.
    ALTER PROCEDURE delAuth
    @topNr int
    AS

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

Posting Permissions

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