Solved

Update Top [percent]

Posted on 2001-07-02
3
1,685 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 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 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