SQL update column via loop

-- Grab the max ext_ref_id and add one.
DECLARE @MAX_EXT_REF_ID AS INTEGER
 
SELECT @MAX_EXT_REF_ID = MAX(CAST(REPLACE(EXT_REF_ID, '_BLC', '') AS INTEGER)) + 1 
FROM [TBL_BLUECARE_CANDIDATES] 
WHERE [EXT_REF_ID] like '%_BLC'
 
SELECT @MAX_EXT_REF_ID -- RETURNS 78656
 
 
--SELECT ALL USERS THAT DO NOT HAVE '_BLC' in the [EXT_REF_ID]
SELECT [EXT_REF_ID]
FROM   [TBL_BLUECARE_CANDIDATES]
WHERE  [EXT_REF_ID] not like '%_BLC'
ORDER BY [EXT_REF_ID]
 
/* RETURNS DATA LIKE 
 
000
0001
001
002
003003003
007
01
079091
10661
11101110
11986
121212123
12335
1234
 
*/
 
 
-- NEED TO UPDATE ALL [EXT_REF_ID] WITHOUT '_BLC' and APPEND @MAX_EXT_REF_ID + 1 + '_BLC'
--  eg. 78656_BLC
--  eg. 78657_BLC
--  eg. 78658_BLC
--  eg. 78659_BLC
 
BEGIN LOOP 
	FOR EACH RECORD IN QUERY ABOVE
	UPDATE [EXT_REF_ID] = (@MAX_EXT_REF_ID + 1) + '_BLC'
END LOOP

Select allOpen in new window