-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|