question

Mark avatar image
Mark 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?
sql-server-2005t-sqlsql-server-2000update
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Oleg avatar image
Oleg answered
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
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oleg avatar image Oleg commented ·
@Mark Thank you. I would still try **Ctrl + L** both queries because it is highly probable that they are identical to the SQL Server :)
1 Like 1 ·
Mark avatar image Mark commented ·
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.
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
+1 from me, 'cos Oleg's version is so much easier to read. :-)
0 Likes 0 ·
Mark avatar image Mark commented ·
Thanks again for the answer. I gave you credit for answering the question, printed our your SQL statement branded with the "Syntax of Oleg," and tacked it upon my wall for future use.
0 Likes 0 ·
Mark avatar image Mark commented ·
@Oleg, I compared the execution plans. Both execution plans look exactly the same, but your code looks much better. (The #Emplist table was left out in both cases since it wasn't needed.) I'm impressed Oleg, if only I'd learned this long ago...
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.