SQL update query syntax with inner join

user_456532 0
Viewed 102132

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
sql-update sql-server-2000 tsql sql-server sql
3 Answers

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
user_116173880
Related