Renumbering the remaining records in a table after some recs have been deleted

Question

Renumbering the remaining records in a table after some recs have been deleted

Mark Dalley

SSCrazy

Points: 2977
More actions
December 3, 2014 at 7:34 am

#313518

Dear experts
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
I suspect that the answer to this little problem will illumine the articles on window functions that I am currently perusing.
Thanks in advance.
Mark Dalley

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 1

Rough guess here, as there isn't too much information about your table; I'd guess you have some sort of patient ID number, or something like it, to identify the fields?

If so, you can do something like this:

WITH CTE(PatientID, RRANKNew) AS(

SELECT patientID, ROW_NUMBER() OVER(ORDER BY RRANK)

FROM RPL

)

UPDATE RISC_PatientList_TEMP

SET RRANK = RRANKNew

FROM RISC_PatientList_TEMP

INNER JOIN CTE

ON RPL.patientID = CTE.patientID

Basically, you use the CTE to get the patient identifiers, and the new RRANK for the identifier, and put those into a CTE; then, join the CTE to the table to be updated by the identifier, and assign the new RRANK.

Be sure to test this first before doing it in production, though! This is a rough guess, so I can't guarantee the results :-P. Should this be insufficient, please provide more details, and I can try to work up something else.

EDIT: Added a missing parenthesis, astutely noted by Mark below.

- 😀

Mark Dalley SSCrazy Points: 2977 More actions · Answer 2

Hi Andrew

Thanks for the quick response, and sorry about being a bit sketchy on the details. You guessed absolutely right, though. I had a feeling CTEs would come into the picture somewhere.

Being a cautious type, I am working on a copy of the table, not the live one, as you suggest, though the data is the same.

Basically the only thing missing from your solution was a ")" at the end of the CTE. I have added it, tried it on the copy, and it works a treat.

Now off to do the deed on the production version.

Many thanks!

MarkD

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 3

Doh! My apologies; that's what I get for not triple-checking my coding :-D. Glad to know it worked out for you, though!

- 😀

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Andrew Kernodle (12/3/2014)
Rough guess here, as there isn't too much information about your table; I'd guess you have some sort of patient ID number, or something like it, to identify the fields?
If so, you can do something like this:
WITH CTE(PatientID, RRANKNew) AS(
SELECT patientID, ROW_NUMBER() OVER(ORDER BY RRANK)
FROM RPL
)
UPDATE RISC_PatientList_TEMP
SET RRANK = RRANKNew
FROM RISC_PatientList_TEMP
INNER JOIN CTE
ON RPL.patientID = CTE.patientID
Basically, you use the CTE to get the patient identifiers, and the new RRANK for the identifier, and put those into a CTE; then, join the CTE to the table to be updated by the identifier, and assign the new RRANK.
Be sure to test this first before doing it in production, though! This is a rough guess, so I can't guarantee the results :-P. Should this be insufficient, please provide more details, and I can try to work up something else.
EDIT: Added a missing parenthesis, astutely noted by Mark below.

So long as the CTE uniquely identifies a row and includes all referenced columns, you can update it directly, like this:

WITH CTE (PatientID, RRANK, RRANKNew) AS (

SELECT patientID, RRANK, ROW_NUMBER() OVER(ORDER BY RRANK)

FROM RPL

)

UPDATE CTE SET RRANK = RRANKNew WHERE RRANK <> RRANKNew

The WHERE clause here is optional.

It's much more efficient.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Andrew Kernodle SSCertifiable Points: 5314 More actions · Answer 5

Ah, of course! I remembered seeing discussion about updating the CTE to update the table of origin, but it didn't strike me as a solution here. It certainly would chop down the working set if the conditions were right; good catch!

- 😀