Updating Multiple Columns with one UPDATE statement

Question

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?

Answer 1

0

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

answered Aug 23 '10 at 04:53 PM

Oleg
15.4k ● 1 ● 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 '10 at 06:54 AM Mark

+1 from me, 'cos Oleg's version is so much easier to read. :-)

Aug 24 '10 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 '10 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 '10 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 '10 at 01:01 PM Mark

(comments are locked)

Updating Multiple Columns with one UPDATE statement

Follow this question

Related Questions