Yes, there's a better way -- use a temporary table:
CREATE TEMPORARY TABLE minopen
SELECT Account_Number, MIN(Open_DATE) AS min_open_date
FROM Table1
GROUP BY Account_Number;
UPDATE Table2, minopen
SET Table2.Open_Date = minopen.min_open_date
WHERE Table2.Account_Number = minopen.Account_Number;
DROP TABLE minopen
Michael
Wesley Furgiuele wrote:
I have a common update that I need to run that is a bottleneck in a
lot of the reports that are being requested. If anybody else has dealt
with a similar situation but with more success, any tips would be
appreciated.
Basically, I have a one-to-many relationship and I want to update
every record in the 'one' table with a MIN result from the 'many'
table.
Although I realize this doesn't work, the query I wish I could run is this:
UPDATE Table2, Table1 SET Table2.Open_Date = MIN( Table1.Open_Date )
WHERE Table2.Account_Number = Table1.Account_Number
Table1
Account_Number CHAR( 10 )
Asset_Number INT( 5 )
Open_Date DATE
Table2
Account_Number CHAR( 10 )
Open_Date DATE
Sample Data
Table1
Account Number Asset Open Date
0000012345 00050 2003-01-01
0000012345 00601 2002-08-10
0001234567 00601 2002-06-15
9003004325 00601 2002-11-16
Table2
Account Number Open Date
0000012345 2002-08-10
0001234567 2002-06-15
9003004325 2002-11-16
What is going on is that Table2 is just a unique list of accounts and
I would like to update it with the earliest open date for any asset
owned by that account number in Table1. Currently, I am using PHP to
issue a set of queries and it can take 1-2 minutes to run through it.
First I get all the account numbers in Table2 (SELECT Account_Number
FROM Table2).
Then I loop through them, one at a time, getting the minimum Open_Date
from Table1 for each account number (SELECT Open_Date FROM Table1
WHERE Account_Number = $account_number ORDER BY Open_Date ASC LIMIT
1). Before moving to the next account number, I run another query to
update Table2 with the Open_Date acquired in the previous query
(UPDATE Table2 SET Open_Date = $open_date WHERE Account_Number =
$account_number).
Is there a better way to do this, either by having MySQL do all the
work, instead of using PHP to loop through? Or is there no way to
avoid looping?
Thanks again for any advice.
Wes
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
