Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
mshsilver
Posting Yak Master
112 Posts |
Posted - 2011-12-06 : 08:53:46
|
Hi,I have a select query and I want to only select the first say 40 rows of the results which will in fact return 70,000 rows. I want to eventually turn the select query into a delete and batch delete but I am struggling to get my head round loops. I was thinking the for loop would be the best and I came across this but it’s not doing what I need.Could someone tell me what I should be googling to find the correct type of loop I am looking for? Maybe there is a specific term for it.Hope you can help. Many thanks.DECLARE @count INT SET @count = 0 WHILE (@count < 40) BEGIN Select * from history h where not exists(select 1 from linkto where lutablename = 'history' and letablename = 'contact' and luniqueid =h.uniqueid )SET @count = (@count + 1) END |
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2011-12-06 : 09:37:46
|
It is part of the FAQ section in sqlteam. You can refer the thread below as well.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77084 |
|
|
mshsilver
Posting Yak Master
112 Posts |
Posted - 2011-12-06 : 10:29:17
|
Can someone just look at this and tell me if it is going to do what i think it is.It will loop through the first 50 records in the contact table where bounce = 'delete' then stop?I tried it with a select statement and it got the first 50 rows but then kept running the query many times so i saw the results of 50 displayed in sql loads.Thanks for looking.set rowcount 50Delete from contact where bounce = 'delete'while @@rowcount > 0beginDelete from contact where bounce = 'delete'end |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-12-06 : 19:29:56
|
Quick answer: No, it will not do what you want. It will continue to DELETE 50 records at a time until the WHERE clause is empty. The "SET ROWCOUNT 50" operates against each DELETE statement; not the accumulation.You could use the TOP clause to produce the results that you want:[CODE]DELETE top 40FROM ContactWHERE bounce = 'delete'[/CODE]This will delete a maximum of 40 records for your stated condition.Your loop, using a SELECT statement, would produce an infinite loop if any records match.HTH=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
|
|
|
|
|