?
Solved

Update Top [percent]

Posted on 2001-07-02
3
Medium Priority
?
1,829 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

649 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