x

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?

more ▼

asked Aug 23, 2010 at 02:30 PM in Default

avatar image

Mark
2.6k 24 27 31

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

more ▼

answered Aug 23, 2010 at 04:53 PM

avatar image

Oleg
17.2k 3 7 28

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.

Aug 24, 2010 at 06:54 AM Mark
  • from me, 'cos Oleg's version is so much easier to read. :-)

Aug 24, 2010 at 07:46 AM ThomasRushton ♦♦

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.

Aug 24, 2010 at 02:27 PM Mark

@Mark Thank you. I would still try Ctrl + L both queries because it is highly probable that they are identical to the SQL Server :)

Aug 24, 2010 at 02:39 PM Oleg

@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...

Aug 25, 2010 at 01:01 PM Mark
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x1069
x501
x164

asked: Aug 23, 2010 at 02:30 PM

Seen: 16372 times

Last Updated: Aug 23, 2010 at 02:30 PM

Copyright 2016 Redgate Software. Privacy Policy