title: Update table based on conditions from another table,that include functions
body: <p>I have 2 mysql tables like in the example below:</p>
  
  <p>CARS:                   </p>
  
  <pre><code>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   
  </code></pre>
  
  <p>RACES:                  </p>
  
  <pre><code>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        
  </code></pre>
  
  <p>I want to Add data on Races table at column Exp_Time based on data from CARS table. 
  For example:</p>
  
  <pre><code>RACES.Exp_Time=AVG(CARS.Time) 
  WHERE
  CARS.CarID=RACES.CarID
  CARS.Dis=RACES.Dis
  CARS.Grd=RACES.Grd
  CARS.Date&lt;RACES.Date
  </code></pre>
  
  <p>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.<br>
  The problem is getting the date condition from RACES table. 
  I do this query:</p>
  
  <pre><code>UPDATE `RACES` c
  INNER JOIN (
  SELECT CARS.CarID, CARS.Dis, CARS.Grd, CARS.Date, AVG(Time) AS `Exp_Time`
  FROM CARS
  WHERE CARS.Date&lt;'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
  </code></pre>
  
  <p>And it works when I type myseld the date - 2011-11-11
  I don't know how to get the data from RACES table. </p>
  
  <p>Can somebody help?
  Thanks in advance!
  Ivan</p>