471,066 Members | 1,633 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

UPDATE a table with aggregate results for multiple columns

Hi everyone. I am updating a table with aggregate results for multiple
columns. Below is an example of how I approached this. It works fine
but is pretty slow. Anyone have an idea how to increase performance.
Thanks for any help.

UPDATE #MyTable
SET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChanged
else null end)
From HREH
Where #MyTable.HRCo=HREH.HRCo and
#MyTable.HRRef=HREH.HRRef ),
TerminationDate=(select Max(Case When Type = 'N' then
DateChanged else null end)
From HREH
Where #MyTable.HRCo=HREH.HRCo and
#MyTable.HRRef=HREH.HRRef ),
ReHireDate=(select MAX(Case When Code = 'HIRE' then
DateChanged else null end)
From HREH
Where #MyTable.HRCo=HREH.HRCo and #MyTable.HRRef=HREH.HRRef )

Jan 8 '07 #1
2 7053
You might get more speed by creating a temp table (or subquery)
from HREH for each HRCo and HRRef pair, populated with MIN(),
MAX() and MAX(), with GROUP BY HRCo and HRRef. Then update
#MyTable from that, which has a single row per case. This may
depend on the relative sizes of HREH and #MyTable. If HREH has
a lot more cases than #MyTable, the temp table or subquery could
be from the join of the the two, to limit it to the cases present in
#MyTable.

Jim

Jan 8 '07 #2
eighthman11 (rd******@nooter.com) writes:
Hi everyone. I am updating a table with aggregate results for multiple
columns. Below is an example of how I approached this. It works fine
but is pretty slow. Anyone have an idea how to increase performance.
This sort of queries usually run faster if you rewrite them into
the proprietary UPDATE FROM:
UPDATE #MyTable
SET Hiredate = h.Hiredate,
TerminationDate = h.TerminationDate,
ReHireDate = h.ReHireDate
FROM #MyTable m
JOIN (SELECT HRCo, HRRef,
HireDate = Min(CASE Code WHEN 'OHDATE' THEN DateChanged END),
TerminationDate = MIN(CASE Type WHEN 'N' THEN DateChanged END),
ReHireDate = MAX(CASE Code WHEN 'HIRE' THEN DateChanged END)
FROM HREH
GROUP BY HrCo, HRRef) AS h ON m.HRCo = h.HRCo
AND m.HRRef = h.HRRef

The thing in parentheses is a derived table. Think of this as a logical
temp table. It is not materialized, and the computation order can very
well be different. Derived tables are part of ANSI SQL. What is
proprietary, and thus not portable, is the use of FROM-JOIN in UPDATE.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 8 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by Dave | last post: by
8 posts views Thread by Lauren Quantrell | last post: by
17 posts views Thread by kalamos | last post: by
9 posts views Thread by Dom Boyce | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.