CArnold
asked on
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did it!
Thanks!
Thanks!
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