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] >
