T-SQL looping through DB & updating records? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

T-SQL looping through DB & updating records?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-28 : 13:35:16

alex writes "Ok, here's the situation. I have a SQL Server 7.0 table of bands. Each time someone views a band's page on the corresponding website, the bandhits field in the db table gets incremented by 1 for that particular band. I then wanted to be able to view a band's overall ranking within the table based on the bandhits field. This would similar to Amazon.com's sales ranking for each book.

The query below successfully returns any particular band's current rank:

DECLARE @total int
DECLARE @greater int
SELECT @total = COUNT(*) FROM bands
SELECT @greater = COUNT(*) FROM bands WHERE bandhits > (SELECT bandhits FROM bands WHERE bandname = 'zao')
SELECT @greater + 1

This is my problem. I don't want to have to execute this expensive query every time someone views a band page (i want a band's ranking to appear on their respective web page). So I was thinking of adding a column to the bands table to include their rank. I would then periodically (once a day or something) run the query above to determine the rank for each band and have the result entered into the new field, bandrank.

How can I modify the SQL above so that it loops through everyband, gets the ranking for that particular band, and then writes a result to that band's bandrank field? Can you do For... Each loops in T-SQL? I'm a bit stumped. Thanks."

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-12-28 : 15:02:01

Well, part of your expense is coming from the SELECT @total = COUNT(*) FROM Bands statement. You're not using @total anywhere else in the code you provided. Are you using this somewhere else? If not, ditch it.

Next, the answer is NO, there is not a For ...Each... and even if there was one, it would be inefficient to do. And while we're on that topic, don't use DECLARE CURSOR either. It's inefficient also.

Instead, you probably want a correlated subquery. You're close with you SELECT @greater... which is just using a simple subquery. A correlated subquery would look something like


SELECT COUNT(*) 
FROM bands t1
WHERE bandhits > 
  (SELECT bandhits 
   FROM bands t2
   WHERE t2.band_primary_key = t1.band_primary_key
   )

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

nr
SQLTeam MVY

12543 Posts

Posted - 2001-12-29 : 15:55:31

If you wanted to update one at a time you could so it by (you don't have to use bandname - any unique indexed field will do).

declare @bandname varchar(?) ,
@maxbandname varchar(?)
select @maxbandname = max(bandname) ,
maxbandname = ''
from bands

while @bandname < @maxbandname
begin
select @bandname = min(bandname) from bands where bandname > @bandname
<<
add code to update rec for @bandname here
>>
end

This is usually one of the slowest ways of doing thing - usually only used for calling SPs or external functions with data.
The corellated subquery will update every row and lock the table for the duration so may cause problems on a large table. A compromise between the two is to update in batches by using a max and min value or holding the record keys to update in a temp table - also means if it fails then you don't have to start from the beginning again..

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources