Menu
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 
442
SQL UPDATE JOIN could be used to update one table using another table and join condition.
The MySQL Update Join is used for executing the update statement together with the implementation of INNER JOIN and LEFT JOIN MySQL clauses in the server. This Update JOIN clause in MySQL helps to retrieve the data records from the related database tables along with modifying them with the query.
Specify the column and value of the column that we want to update. We use the Set statement for specifying the values. Use SQL Join operator and specify the table name with join conditions. We can either use an Inner Join or Left Join in this predicate.
Below is the basic syntax of Inner Join. Inner Join syntax basically compares rows of Table1 with Table2 to check if anything matches based on the condition provided in the ON clause. When the Join condition is met, it returns matched rows in both tables with the selected columns in the SELECT clause.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
