I think I get the question but not sure, so here's a shot in the dark:

DECLARE @Table1 TABLE (software_name_raw VARCHAR(100))

INSERT INTO @Table1

SELECT 'microsoft outlook 2007'

UNION ALL SELECT 'microsoft 2007 outlook'

UNION ALL SELECT 'microsoft out look 2007'

UNION ALL SELECT 'microsoft 2007 office'

UNION ALL SELECT 'microsoft office 2007'

;WITH Table2 (software_name_raw, software_name) AS (

SELECT 'microsoft outlook 2007', 'outlook 2007'

UNION ALL SELECT 'microsoft 2007 outlook', 'outlook 2007'

UNION ALL SELECT 'microsoft out look 2007', 'outlook 2007'

UNION ALL SELECT 'microsoft 2007 office', 'office 2007'

UNION ALL SELECT 'microsoft office 2007', 'office 2007')

UPDATE a

SET software_name_raw = software_name

FROM @Table1 a

INNER JOIN Table2 b ON a.software_name_raw = b.software_name_raw

SELECT *

FROM @Table1

The error message you are getting is because you can't update through a CTE when you use DISTINCT or have derived columns (like your LTRIM/RTRIM).

You also may need to take care with this approach if your database happens to be using a case sensitive collation string.