Update Top [percent]

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?
LVL 1
CArnoldAsked:
Who is Participating?
 
nigelrivettCommented:
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
 
nigelrivettCommented:
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
 
CArnoldAuthor Commented:
That did it!
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.