34

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
3
  • @OMG Ponies- Thanks for looking, but this query has the same effect as the original (not working due to syntax error). I believe this is because SQL server ignores spaces and carriage returns in a query.
    – MAW74656
    Commented Oct 5, 2010 at 19:54
  • I just reformatted what you posted--easier to read, easier to help
    – OMG Ponies
    Commented Oct 5, 2010 at 19:55
  • Ok ponies, I understand. I was working off an example, online... tek-tips.com/faqs.cfm?fid=1958 But your way is better.
    – MAW74656
    Commented Oct 5, 2010 at 19:59

3 Answers 3

64

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
8
  • +1: SS2000 Update documentation
    – OMG Ponies
    Commented Oct 5, 2010 at 19:54
  • @Joe Stefanelli - Close, but the parser doesn't like the names when an alias is set.
    – MAW74656
    Commented Oct 5, 2010 at 19:58
  • 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
    – MAW74656
    Commented Oct 5, 2010 at 19:58
  • @MAW74656: I know my eyes are getting worse as I age, but isn't that what I gave you? Commented Oct 5, 2010 at 20:00
  • @Joe Stefanelli - Hmmm, your right. Maybe I'm the one with the bad eyes!
    – MAW74656
    Commented Oct 5, 2010 at 20:02
4

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
1
  • 1
    This is true in case of MySQL Commented Sep 23, 2020 at 11:50
0

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
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.