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

2022-01-17 00:00:00 sql-update sql tsql sql-server sql-server-2000

谁能在这个查询中找到我的错误?我正在使用 SQL Server 2000，我想将 CostEntry 表中的所有条目更新为 ActiveCostDetails 表中的相应值.where 子句确实适用于 select 语句.

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.

    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

推荐答案

SET 需要位于查询的 FROMJOINWHERE 部分之前.p>

The SET needs to come before the FROMJOINWHERE 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

相关文章