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]
