asked
Updating Multiple Columns with one UPDATE statement
Every time I use this syntax, I get the idea that there is probably a better way. All I want to do is update two columns in a table from another table. Am I doing this the best way? SQL-2000 is preferable, but I'd like to see other solutions too. UPDATE #ActEmpl SET HireDateFY = AE.HireDateFY, HirePeriod = AE.HirePeriod FROM #ActEmpl INNER JOIN (SELECT EmployeeID, CASE WHEN MONTH(e.HireDate) = 1 THEN Year(e.HireDate) ELSE YEAR(e.HireDate) + 1 END as HireDateFY, CASE WHEN MONTH(e.HireDate) = 1 THEN 12 ELSE MONTH(e.HireDate) - 1 END as HirePeriod FROM Employee e WHERE e.EmployeeID IN (SELECT EmployeeID FROM #EmpList) ) AE ON AE.EmployeeID = #ActEmpl.EmployeeID Also, note that the WHERE clause includes a limit on the employeeIDs. Is that worthwhile since there is an INNER JOIN on that table anyway?
Your query looks fine. The where clause including the limit on the EmployeeID is definitely worthwhile, bacause there might be some records in the #ActEmpl which are not present in #EmpList, so those will not be updated and it looks like this is what you want. If I were to write the same update query, I would probably opt to restate my **IN** part as a join and also move the sub-select out like this: update a set HireDateFY = case when month(e.HireDate) = 1 then year(e.HireDate) else year(e.HireDate) + 1 end, HirePeriod = case when month(e.HireDate) = 1 then 12 else month(e.HireDate) - 1 end from #ActEmpl a inner join Employee e on a.EmployeeID = e.EmployeeID inner join #EmpList list on e.EmployeeID = list.EmployeeID; There is no guarantee that this will generate a different execution plan though. You can **Ctrl + L** both queries to find out. Oleg
Thank you Oleg. I think that I like your query better since it puts the case statements closer to the columns to be updated. I'll have to try that. As for the where statement, I should have mentioned that both the #Emplist and #ActEmpl tables always have the same records (i.e., the same employee IDs listed). But in that case, it sounds like I can skip the where statement for employee IDs.
+1 from me, 'cos Oleg's version is so much easier to read. :-)
