Yeah I thought of that but was hoping not to have to use a temp table. Thanks!
Jeff -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, November 05, 2004 9:25 AM To: Jeff McKeon Cc: [EMAIL PROTECTED] Subject: Re: Update query help Break it down into two steps. Compute your new values by customerid, then update your customer table with your computed data. CREATE TEMPORARY TABLE tmpFirstTran SELECT CustID, min(Datestamp) as mindate from Transactions group by CustID; update Customer c INNER JOIN tmpFirstTran ft ON ft.CustID = c.ID SET c.First_Transaction = ft.mindate; DROP TEMPORARY TABLE tmpFirstTran; Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Jeff McKeon" <[EMAIL PROTECTED]> wrote on 11/05/2004 09:04:06 AM: > I have two tables. One has a list of customers. The other has a > record of customer transactions including unix datestamps of each > transaction. > > I've added a field to the customer table called "First_Transaction" > > I want to update this field with the datestamp of the first > transaction for each customer from the "Transaction" table. > > I tried this... > > UPDATE Customer,Transactions set Customer.First_Transaction = > MIN(Transactions.Datestamp) > Where Customer.ID = Transactions.CustID > > But this doesn't work because of MIN() grouping. I'm stumped, anyone > know how to accomplish this? > > Thanks, > > Jeff > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
