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

Mark gravatar image

Mark
2.6k 23 25 27

(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

Oleg gravatar image

Oleg
15.9k 2 4 24

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
+1 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1951
x991
x476
x133

asked: Aug 23, 2010 at 02:30 PM

Seen: 10253 times

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