UPDATE Records and Increment Field Value +1

Here is my SQL query:


update ##tmp_results
set StratLevel = 3
where prov_id  in (select TOP 50 PERCENT Prov_ID FROM ##tmp_results ORDER BY ScoreOverall DESC)


Basically, it is dumping results that meet the search criteria into a ##global_temp table.

I would like to keep the functionality of this query intact AND be able to add an incremented value (+1 for each row updated) to a field (In the example below, "Rank" would be the field containing the incremented values).

Example:

ScoreOverall        Prov_ID           Rank
------------        -------           ----
100                 Gary                1
 87                 Bob                 2
 85                 Neil                3
 79                 Jack                4


Is there a way I can add this funtionality to the existing query?

I am trying to stay away from using a cursor to accomplish this procedure -- they are PAINFULLY slow!

LVL 1
CArnoldAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
Try this:

update ##tmp_results
set StratLevel = 3,
Rank = 1+(SELECT count(*) FROM ##tmp_results i WHERE i.ScoreOverall < ##tmp_results.ScoreOverall)
where prov_id  in (select TOP 50 PERCENT Prov_ID FROM ##tmp_results ORDER BY ScoreOverall DESC)

Cheers
0
 
CArnoldAuthor Commented:
I have upped the ante to 200 points.
Any responses?
0
 
CArnoldAuthor Commented:
Works great!

Well... almost.  I had to turn your '<' sign around to make it work properly.  I never thought about doing it that way.

Great answer.  The points are yours!

Thanks,
Charles
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.