SQL update query syntax with inner join

Once you have set an alias name for the table, you cannot use the table name. Try your query this way, it will work.

UPDATE CostEntry CE 

        INNER JOIN 
            ActiveCostDetails AD 
            ON (CE.lUniqueID = AD.UniqueID)

           SET CE.sJobNumber = AD.JobNumber

         WHERE CE.SEmployeeCode = '002'
           AND SubString(CostCentre, 1, 1) = sDepartmentCode
           AND substring(CostCentre, 3, 1) = sCategoryCode
           AND substring(CostCentre, 5, 2) = sOperationCode

The SET needs to come before the FROM\JOIN\WHERE portion of the query.

UPDATE CE
SET sJobNumber = AD.JobNumber
FROM CostEntry CE 
    INNER JOIN ActiveCostDetails As AD 
        ON CE.lUniqueID = AD.UniqueID
WHERE CE.SEmployeeCode = '002'
    AND SubString(CostCentre, 1, 1) = sDepartmentCode
    AND substring(CostCentre, 3, 1) = sCategoryCode
    AND substring(CostCentre, 5, 2) = sOperationCode