?
Solved

Update Top [percent]

Posted on 2001-07-02
3
Medium Priority
?
1,773 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Accepted Solution

by:
nigelrivett earned 400 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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

801 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