Change the condition based on a row value for the same Id ,update the substring value of the other rows based on the ID

I am developing a script that requires a change in condition based on the above row of a column , the common is it shares the same ID and (Records row for having the same number)

CREATE TABLE #results
(
ID INT,
Column2 NVARCHAR(50),
Column3 INT,
Column4 INT,
Column5 INT,
Column6 INT,
Recurring_Value INT
)
-- Insert sample data
INSERT INTO #results
(ID, Column2, Column3, Column4, Column5, Column6)
VALUES
(2, 'F2AGE', 1, 1, 1, 2),
(2, 'M1AGE', 1, 1, 1, 1),
(2, 'M3AGE', 1, 1, 1, 3),
(2, 'NB1HOH', 1, 1, 1, 1),
(2, 'NB2HOH', 1, 1, 1, 2),
(2, 'NB3HOH', 1, 1, 1, 3),
(2, 'NB3WORK', 1, 1, 1, 3),
(10, 'F10AGE', 1, 1, 1, 10),
(10, 'F3AGE', 1, 1, 1, 3),
(10, 'F5AGE', 1, 1, 1, 5),
(10, 'F7AGE', 1, 1, 1, 7),
(10, 'F8AGE', 1, 1, 1, 8),
(10, 'F9AGE', 1, 1, 1, 9),
(10, 'M1AGE', 1, 1, 1, 1),
(10, 'M2AGE', 1, 1, 1, 2),
(10, 'M4AGE', 1, 1, 1, 4),
(10, 'M6AGE', 1, 1, 1, 6),
(10, 'NB10HOH', 1, 1, 1, 10),
(10, 'NB1HOH', 1, 1, 1, 1),
(10, 'NB1WORK', 1, 1, 1, 1),
(10, 'NB4HOH', 1, 1, 1, 4),
(10, 'NB4WORK', 1, 1, 1, 4),
(10, 'NB5HOH', 1, 1, 1, 5),
(10, 'NB5WORK', 1, 1, 1, 5),
(10, 'NB6HOH', 1, 1, 1, 6),
(10, 'NB6WORK', 1, 1, 1, 6),
(10, 'NB7HOH', 1, 1, 1, 7),
(10, 'NB8HOH', 1, 1, 1, 8),
(10, 'NB8WORK', 1, 1, 1, 8),
(10, 'NB9HOH', 1, 1, 1, 9);
-- Update the table based on the given conditions
WITH AgeRows AS (
SELECT ID, Column6, SUBSTRING(Column2, 1, 1) AS Gender
FROM #results
WHERE Column2 LIKE '%[MF][1-9]AGE%'
),
UpdateRows AS (
SELECT r.ID, r.Column2, r.Column6, a.Gender
FROM #results r
JOIN AgeRows a ON r.ID = a.ID
WHERE r.Column2 LIKE '%NB[1-9]WORK%' OR r.Column2 LIKE '%NB[1-9]HOH%'
)
UPDATE r
SET Column2 = REPLACE(r.Column2, 'NB', u.Gender)
FROM #results r
JOIN UpdateRows u ON r.ID = u.ID AND r.Column2 = u.Column2;
-- Select the updated data
SELECT * FROM #results;

I need to assign F2AGE for recurringvalue=2 then update column2 where column2='NB2HOH' and column2='NB2work' rename it to f2hoh and f2work

then same for the next.

The above code does not assign it properly based on multiple row values f[1-19]or M[1-19]

I added an extra row recurringvalue to identify the Age number

I think you forgot an age condition in UpdateRows CTE. Also I did not understand why you need so complex LIKE conditions. I simplified them because they did not pick up 10 in 'F10AGE'. If your data really requires such conditions, then take into account that 'NB10HOH' will not be updated.

-- Update the table based on the given conditions
WITH AgeRows AS (
SELECT ID, Column6, SUBSTRING(Column2, 1, 1) AS Gender
FROM #results
WHERE Column2 LIKE '%AGE'
),
UpdateRows AS (
SELECT r.ID, r.Column2, r.Column6, a.Gender
FROM #results r
JOIN AgeRows a ON r.ID = a.ID and r.Column6 = a.Column6 /*This condition will help to join rows correctly*/
WHERE r.Column2 LIKE 'NB%'
)
UPDATE r
SET Column2 = REPLACE(r.Column2, 'NB', u.Gender)
FROM #results r
JOIN UpdateRows u ON r.ID = u.ID AND r.Column2 = u.Column2;
-- Select the updated data
SELECT * FROM #results;