具有内部联接的SQL更新查询语法

Can anyone find my error in this query? I'm using SQL Server 2000 and I want to update all entries in the CostEntry table to the corresponding value in the ActiveCostDetails table. The where clause DOES work with a select statement.

任何人都可以在此查询中找到我的错误？我正在使用SQL Server 2000，我想将CostEntry表中的所有条目更新为ActiveCostDetails表中的相应值。 where子句可以使用select语句。

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

2 个解决方案

#1

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

SET需要在查询的FROM \ JOIN \ WHERE部分之前。

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

#2

This should work

这应该工作

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

#1