Is it necessary to replace this cursor in SQL Server 2005?

Question

The Problem

I have a cursor that I am trying to replace (perhaps unnecessarily) in an attempt to clean up a stored procedure. Essentially what it is doing is counting each note for each member in a temp table and then assigning each of those notes a sequential value into the field NOTE_COUNTER (int) of the temp table. Here is the original code for it with the variables:

@lUPI_current          CHAR(32),
@lCASE_SEQ_current     INT,
@lDIFF_ENCOUNTER_DATE  INT,
@lcounter              INT

SET @lUPI_current = ''
SET @lCASE_SEQ_current = ''  
SET @lDIFF_ENCOUNTER_DATE = 0 
SET @lcounter = 0

DECLARE CURSOR_NOTE_COUNTER CURSOR FOR 
SELECT 
    MEMBER, 
    CASE_SEQ,
    ENCOUNTER_DATE
FROM
    #TMP_NOTES  
WHERE
    TOTAL_CALLS > 1      
ORDER BY 
    MEMBER, 
    CASE_SEQ,  
    ENCOUNTER_DATE

OPEN CURSOR_NOTE_COUNTER

FETCH NEXT FROM CURSOR_NOTE_COUNTER 
INTO 
  @lUPI_current,  
  @lCASE_SEQ_current,
  @lDIFF_ENCOUNTER_DATE

WHILE (@@FETCH_STATUS = 0)
BEGIN 
    SET @lcounter = (
                        SELECT 
                            MAX(NOTE_COUNTER) 
                        FROM
                            #TMP_NOTES
                        WHERE 
                            MEMBER_UPI = @lUPI_current
                            AND CASE_SEQ = @lCASE_SEQ_current
                    )
UPDATE #TMP_NOTES   
    SET 
        NOTE_COUNTER = @lcounter + 1
    FROM #TMP_NOTES
    WHERE
        MEMBER_UPI = @lUPI_current
        AND CASE_SEQ = @lCASE_SEQ_current
        AND DIFF_ENCOUNTER_DATE = @lDIFF_ENCOUNTER_DATE

    FETCH NEXT FROM CURSOR_NOTE_COUNTER 
    INTO 
        @lUPI_current,
        @lCASE_SEQ_current,
        @lDIFF_ENCOUNTER_DATE
END

What I Have Tried

I tried replacing this with a CTE because of the notion that cursors should be avoided in favor of working on sets of data. Here was my attempt at that, the stumbling block being the ambiguity of the column names since it is working on the same table.

WITH RecordsBeingUpdated AS
    (
        SELECT
            MEMBER_UPI,
            CASE_SEQ,
            DIFF_ENCOUNTER_DATE,
            ROW_NUMBER() OVER (PARTITION BY MEMBER_UPI, CASE_SEQ, DIFF_ENCOUNTER_DATE ORDER BY DIFF_ENCOUNTER_DATE ASC) AS RowNum
        FROM
            #TMP_NOTES  
        WHERE
            TOTAL_CALLS > 1      
    ) 

UPDATE #TMP_NOTES   
    SET
        NOTE_COUNTER = R.RowNum
    FROM
        RecordsBeingUpdated AS R
    WHERE
       MEMBER_UPI = R.MEMBER_UPI
       AND CASE_SEQ = R.CASE_SEQ
       AND DIFF_ENCOUNTER_DATE = R.DIFF_ENCOUNTER_DATE

Please tell me if there is a cleaner way to doing this. I am grateful for any time that the community can provide.

Answer 1

So, you can simplify the CTE with some SQL magic:

WITH RecordsBeingUpdated AS 
( 
    SELECT 
        NOTE_COUNTER,
        MEMBER_UPI, 
        CASE_SEQ, 
        DIFF_ENCOUNTER_DATE, 
        ROW_NUMBER() OVER (PARTITION BY MEMBER_UPI, CASE_SEQ ORDER BY DIFF_ENCOUNTER_DATE ASC) AS RowNum 
    FROM 
        #TMP_NOTES   
    WHERE 
        TOTAL_CALLS > 1       
)  

UPDATE RecordsBeingUpdated
SET    NOTE_COUNTER = RowNum 

Essentially, the virtual table built for the CTE has direct reference back to the concrete tables in the selection. This means you can update the CTE directly and SQL will update the corresponding concrete table within, provided the target table is the FROM table (not a joined) and there is no chance of ambiguity in what needs to be updated.

(Removed DIFF_ENCOUNTER_DATE from partition per comments).