SQL update query syntax with inner join

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

99,054

Solution 1

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

Solution 2

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

99,054

Author by

MAW74656

Updated on July 21, 2020

Comments

MAW74656 almost 4 years

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

OMG Ponies over 13 years

+1: SS2000 Update documentation
MAW74656 over 13 years

@Joe Stefanelli - Close, but the parser doesn't like the names when an alias is set.
MAW74656 over 13 years

This worked:UPDATE CE SET CE.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
Joe Stefanelli over 13 years

@MAW74656: I know my eyes are getting worse as I age, but isn't that what I gave you?
MAW74656 over 13 years

@Joe Stefanelli - Hmmm, your right. Maybe I'm the one with the bad eyes!
OMG Ponies over 13 years

In MAW74656's comment, sjobnumber has the table alias
Joe Stefanelli over 13 years

@MAW74656: No problem either way. Since this worked, would you please mark this as the accepted answer?
Joe Stefanelli over 13 years

@OMG Ponies: See, I told you my eyes were getting bad! :-)
Kedar Javalkar over 3 years

This is true in case of MySQL