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

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

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

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