473,220 Members | 2,217 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,220 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 7169
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Dave | last post by:
I have 2 tables, one with names, and another with addresses, joined by their CIVICID number (unique to the ADDRESSINFO table) in Oracle. I need to update a field in the NAMEINFO table for a...
1
by: Caroline | last post by:
I need to update a table by copying a column from another table (having the same structure, but on another database), from the record having the same primary key. 1 - What is the correct...
8
by: Lauren Quantrell | last post by:
In VBA, I constructed the following to update all records in tblmyTable with each records in tblmyTableTEMP having the same UniqueID: UPDATE tblMyTable RIGHT JOIN tblMyTableTEMP ON...
7
by: Drew | last post by:
I have a db table like the following, UID, int auto-increment RegNo Person Relation YearsKnown Now here is some sample data from this table,
17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
22
by: pbd22 | last post by:
hi. I am having probelms with an update statement. every time i run it, "every" row updates, not just the one(s) intended. so, here is what i have. i have tried this with both AND and OR and...
4
by: wrldruler | last post by:
Hello, First, I know it's against "Access Law" to save calculations in a table, but....I want/need to. I currently have sub-totals being calculated inside a form, using DMax, DCount, and...
5
by: Bogdan | last post by:
Hi, I have a stored procedure that uses JOINs to return columns from multiple tables. I also have another stored proc that that takes a series of params and updates multiple tables. I used the...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

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.