Solved

Update Top [percent]

Posted on 2001-07-02
3
1,670 Views
Last Modified: 2008-03-17
I am trying to make a stored procedure capable of updating ONLY a top x% of the records in a table.  Currently the code looks like this:

DECLARE csr_Result CURSOR FOR
     SELECT TOP 50 PERCENT Prov_ID, StratLevel FROM ##tmp_Results ORDER BY ScoreOverall DESC
     
OPEN csr_Result

FETCH NEXT FROM csr_Result INTO @PID, @STRAT

WHILE @@FETCH_STATUS = 0
BEGIN
     IF @STRAT <> 3
     Begin
          UPDATE ##tmp_Results SET StratLevel = 3 WHERE prov_id = @pid
     END
     FETCH NEXT FROM csr_Result INTO @PID, @STRAT
END

CLOSE csr_Result
DEALLOCATE csr_Result


This code works, but it is painfully slow (it loops through a returned recordset and updates each record individually).  Is there a command or function I can use to UPDATE TOP x PERCENT?  If not, does anyone have any ideas on how I can achieve the same result with more efficient code?
0
Comment
Question by:CArnold
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
nigelrivett earned 100 total points
ID: 6245526
update ##tmp_Results
set StratLevel = 3
where prov_id  in (select TOP 50 PERCENT Prov_ID FROM ##tmp_Results ORDER BY ScoreOverall DESC)
and StratLevel <> 3
   
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6245543
Or maybe

select @id = max(Prov_ID)
from (select TOP 50 PERCENT Prov_ID FROM ##tmp_Results ORDER BY ScoreOverall DESC) as a

update ##tmp_Results
set StratLevel = 3
where prov_id  <= @id
and StratLevel <> 3
   


0
 
LVL 1

Author Comment

by:CArnold
ID: 6246470
That did it!
Thanks!
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question