Can you update using a LIKE operator on JOIN w/ TSQL?

Question

I'm doing a large import from a comma delimited file and want to lookup an employer id during the sproc that is executed at the end of the import process. The issue I'm having is that my LIKE doesn't seem to work ... so I wanted to see if the syntax is correct.

Note - This will update all the records = the employer name but anything LIKE 'foo%' will not get updated

UPDATE UnitOfWork
SET EmployerId = SG.EmployerId
FROM UnitOfWork UO    
LEFT JOIN SpecialtyGroup SG ON SG.Name LIKE UO.EmployerName + '%'
WHERE SG.EmployerId IS NOT NULL

Is it possible to do a LIKE in this way or do I need to do another update outside of this join?

Answer 1

For MS SQL 2008:

MERGE
UnitOfWork UO
USING SpecialtyGroup SG
   ON SG.Name LIKE UO.EmployerName + '%'
WHEN MATCHED THEN
   UPDATE
   SET UO.EmployerId = SG.EmployerId

For MS SQL 2005:

WITH so AS
(
  SELECT UO.EmployerID, SG.EmployerID AS newEmployerID
  FROM UnitOfWork UO    
  LEFT JOIN SpecialtyGroup SG ON SG.Name LIKE UO.EmployerName + '%'
  WHERE SG.EmployerId IS NOT NULL
) 
UPDATE so
SET EmployerId = newEmployerID