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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Loop question

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
Go to Top of Page

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 50

Delete from contact where bounce = 'delete'


while @@rowcount > 0

begin

Delete from contact where bounce = 'delete'

end

Go to Top of Page

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 40
FROM Contact
WHERE 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
Go to Top of Page
   

- Advertisement -