title: Hard SQL Update query with aggreration
body: <p>Table Structure:</p>
  
  <pre><code>Auto(model int (key),year int (key),country varchar)
  Store(name varchar(key),country varchar,model int (key),price int)
  </code></pre>
  
  <p>Updating stores that only sells models that are not manufactured in the country that the store exists in, in this way: if the price of the model in the store is maximal in relation to all models in all stores of the same country, then change the price with the average of all the prices of the same model in all the stores of the same country. (Auto.country is where it manufactured).</p>
  
  <p>Thats where I am at. not sure if its correct.</p>
  
  <pre><code>UPDATE ... 
  SET ... 
  FROM ... 
  WHERE model in 
      (
      SELECT DISTINCT model 
      FROM Auto,Store 
      WHERE Auto.model=Store.model 
          AND Auto.country!=Store.country
      )
  </code></pre>
  
  <p>Can't figure this out.
  Thanks for your help.</p>