SQL Server : problem updating a counter type column in a table. All rows get the same value and they-CodePudding

I'm new to SQL and looking for assistance updating every record in a table column with a calculated value. I'm sure I am overthinking this!

I need to loop through all the records (870K ) and increment a counter variable with a value between 01 and 99 (2 digits) which will replace the value in the MemoKeyExtension (NChar 2) column in my table.

To keep it simple, you can assume this table just has a uniqueid column, an account number column (int), and the MemoKeyExtension (nChar2) column.

DECLARE @COUNT as Int
SET @COUNT = 0

DECLARE Mem_Cursor CURSOR FOR
    SELECT MemoKeyExtension FROM Customer.Memo
OPEN MEM_Cursor
FETCH NEXT FROM MEM_Cursor 
WHILE @@FETCH_STATUS = 0
    BEGIN
    IF @COUNT < 99
        BEGIN
        SET @COUNT = @COUNT   1
        END
    ELSE 
        BEGIN
        SET @COUNT = 1
        END

        UPDATE Customer.Memo
        SET MemoKeyExtension = REPLACE(STR(@COUNT,2),' ','0') 
        WHERE CURRENT OF MEM_Cursor
        FETCH NEXT FROM MEM_Cursor 
    END

CLOSE MEM_Cursor
DEALLOCATE MEM_Cursor

Example of desired results:
MemoId                                  MemoKeyExtension
F630A22A-6BAA-4F86-84FB-0823FCAD95A0    95
7BE0E85D-9CA8-4E01-888E-781D5356EDB8    96
5D7BD8F2-5858-4A8F-96B0-E93D44030925    97
9520A24A-5168-41E8-ADFF-429513EB3693    98
5890818B-6EBB-4CD4-94C6-D467679427E5    99
B730FD54-F231-494E-A7F0-E7162F2EDD10    01
578A2457-39DF-41A4-953C-4A3C9E69F394    02
F316C39C-6ACA-4A7C-A401-39518F2EF9A1    03

Thank you for any help you can provide.

CodePudding user response：

Mostly guessing here since we have no table definitions to work with. If this doesn't work it should be really close.

with MyNumbers as
(
    select NewVal = right('0'   convert(varchar(2), ROW_NUMBER() over (order by (select null)) % 99), 2)
        , MemoId
    from Customer.Memo m
)
update m
set MemoKeyExtension = case when NewVal = '00' then '99' else n.NewVal end
from Customer.Memo m
join MyNumbers n on n.MemoId = m.MemoId