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