Update table based on conditions from another table,that include functions

Question

I have 2 mysql tables like in the example below:

CARS:

RaceID CarID    Dis Grd Date            Time    
8      1    200 A   2010-10-10  20.50
8      2    300 A   2010-10-10  30.50
8      3    200 A   2010-10-10  20.10
9      1    200 A   2010-11-10  20.00
12     1    200 A   2011-12-11  19.50   

RACES:

RaceId  CarID   Dis Grd Date        Exp_Time

10  1   200 A   2011-11-11        
10  2   200 A   2011-11-11        
10  3   200 A   2011-11-11        

I want to Add data on Races table at column Exp_Time based on data from CARS table. For example:

RACES.Exp_Time=AVG(CARS.Time) 
WHERE
CARS.CarID=RACES.CarID
CARS.Dis=RACES.Dis
CARS.Grd=RACES.Grd
CARS.Date<RACES.Date

The idea is that expected Time is AVG from previous races times on same distance and grd. Future races should be excluded from calculation AVG.
The problem is getting the date condition from RACES table. I do this query:

UPDATE `RACES` c
INNER JOIN (
SELECT CARS.CarID, CARS.Dis, CARS.Grd, CARS.Date, AVG(Time) AS `Exp_Time`
FROM CARS
WHERE CARS.Date<'2011-11-11'
GROUP BY CarID, Dis, Grd
)
x ON c.CarID=x.CarID AND c.Dis=x.Dis AND c.Grd=x.Grd
SET c.Exp_Time=x.Exp_Time

And it works when I type myseld the date - 2011-11-11 I don't know how to get the data from RACES table.

Can somebody help? Thanks in advance! Ivan

Answer 1

UPDATE `RACES` c
INNER JOIN (
SELECT CARS.CarID, CARS.Dis, CARS.Grd, CARS.Date, AVG(Time) AS `Exp_Time`
FROM CARS
WHERE CARS.Date < (SELECT  Date 
                   FROM RACES 
                   WHERE    CARS.Date<RACES.Date 
                        AND CARS.Grd=RACES.Grd 
                        AND CARS.CarID=RACES.CarID
                        AND CARS.Dis=RACES.Dis
                        LIMIT 1 
                )
                GROUP BY CarID, Dis, Grd
)
x ON c.CarID=x.CarID AND c.Dis=x.Dis AND c.Grd=x.Grd
SET c.Exp_Time=x.Exp_Time

Answer 2

2011-11-11 I don't know how to get the data from RACES table.

You can move this predicate to the JOIN condition like so:

UPDATE `RACES` c
INNER JOIN (
  SELECT 
     CarID, Dis, Grd, Date, AVG(Time) AS `Exp_Time`
  FROM CARS
  GROUP BY CarID, Dis, Grd
)x  ON c.CarID        = x.CarID 
   AND c.Dis          = x.Dis 
   AND c.Grd          = x.Grd
   AND x.DATE(`Date`) < c.DATE(`Date`)
SET c.Exp_Time = x.Exp_Time;