Another solution is to PIVOT the #temp1 table and join this with the table you want to update

-- Pivot source table and join with table to update

Update #Customer_Profile_Master1

set Co_apcode_1_categ = [1]

, Co_apcode_2_categ = [2]

, Co_apcode_3_categ = [3]

, Co_apcode_4_categ = [4]

FROM (

SELECT lnno

, Category

, Rownumber

FROM #temp1

) AS SourceTable

PIVOT(MAX(Category) FOR Rownumber IN (

[1]

, [2]

, [3]

, [4]

)) AS PivotTable

inner join #Customer_Profile_Master1

on PivotTable.lnno = #Customer_Profile_Master1.lnno