I would create temp table or CTE and then use that to do insert update. The "ID" field is your PK of the table.

IF OBJECT_ID('tempdb..#Source','u') IS NOT NULL

DROP TABLE #Source

SELECT dbsource.ID

,dbsource.software_name_raw

,dbref.software_name_amended

,ToInsert = CASE WHEN dbref.software_name_raw IS NULL

THEN 1

ELSE 0

END

INTO #Source

FROM dbo.BigTable dbsource

LEFT JOIN (

SELECT software_name_raw,software_name_amended

FROM RefTable

GROUP BY software_name_raw,software_name_amended

) dbref

ON dbref.software_name_raw = dbsource.software_name_raw

UPDATE BigTable

SET software_name_raw = src.software_name_amended

FROM #Source src

JOIN dbo.BigTable

ON BigTable.ID = src.ID

WHERE src.ToInsert = 0

INSERT INTO dbo.RefTable

(software_name_raw

,software_name_amended

)

SELECT software_name_raw

,'Needs Updating'

FROM #Source

WHERE ToInsert = 1