代码之家 › 专栏 › 技术社区 › MAW74656

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

sql-update sql-server-2000 tsql sql-server sql

23

MAW74656 · 技术社区 · 14 年前

有人能在这个查询中找到我的错误吗?我使用的是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 回复 | 直到 8 年前

1

52

Joe Stefanelli 14 年前

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

1

SKh 8 年前

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