title: Updating multiple columns with data from subquery in MySQL
body: <p>I am trying to update multiple columns in a row, with data from multiple columns in a subquery.</p>
  
  <p>The following approaches did not work for me, and I can't find different ones that suit my needs:</p>
  
  <pre><code>UPDATE
      beers,
      (SELECT AVG(appearance) AS appearance, AVG(palate) AS palate, AVG(taste) AS taste, AVG(aroma) AS aroma, AVG(overall) AS overall, beer_id FROM reviews) AS review_total
  SET
      beers.appearance = review_total.appearance, 
      beers.palate = review_total.palate, 
      beers.taste = review_total.taste, 
      beers.aroma = review_total.aroma, 
      beers.overall = review_total.overall
  WHERE
      review_total.beer_id = beers.id
  AND
      beers.id = 43
  </code></pre>
  
  <p>I don't get an error for this one, but 5 warnings and the row is not updated:</p>
  
  <pre><code>Query OK, 0 rows affected, 5 warnings (0.01 sec)
  </code></pre>
  
  <p>Show warnings gives me:</p>
  
  <pre><code>+-------+------+----------------------------------------------------+
  | Level | Code | Message                                            |
  +-------+------+----------------------------------------------------+
  | Note  | 1265 | Data truncated for column 'appearance' at row 9991 |
  | Note  | 1265 | Data truncated for column 'palate' at row 9991     |
  | Note  | 1265 | Data truncated for column 'taste' at row 9991      |
  | Note  | 1265 | Data truncated for column 'aroma' at row 9991      |
  | Note  | 1265 | Data truncated for column 'overall' at row 9991    |
  +-------+------+----------------------------------------------------+
  </code></pre>
  
  <p>I know this issue has to do with the data type, but the data type is float, i beleive thats what AVG's result is too:</p>
  
  <pre><code>mysql&gt; describe beers;
  +-------------+---------------+------+-----+---------+----------------+
  | Field       | Type          | Null | Key | Default | Extra          |
  +-------------+---------------+------+-----+---------+----------------+
  | id          | int(11)       | NO   | PRI | NULL    | auto_increment |
  | name        | varchar(90)   | YES  |     | NULL    |                |
  | aroma       | float         | YES  |     | NULL    |                |
  | appearance  | float         | YES  |     | NULL    |                |
  | palate      | float         | YES  |     | NULL    |                |
  | taste       | float         | YES  |     | NULL    |                |
  | overall     | float         | YES  |     | NULL    |                |
  +-------------+---------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>The next query is slightly different:</p>
  
  <pre><code>UPDATE
      beers
  SET
      beers.appearance = review_total.appearance, 
      beers.palate = review_total.palate, 
      beers.taste = review_total.taste, 
      beers.aroma = review_total.aroma, 
      beers.overall = review_total.overall
  FROM
      INNER JOIN (SELECT AVG(appearance) AS appearance, AVG(palate) AS palate, AVG(taste) AS taste, AVG(aroma) AS aroma, AVG(overall) AS overall, beer_id FROM reviews) review_total ON review_total.beer_id = beers.id
  WHERE
      beers.id = 43
  </code></pre>
  
  <p>The error i got for this one is:</p>
  
  <pre><code>ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM INNER JOIN (SELECT AVG(appearance) AS appearance, AVG(palate) AS palate, AV' at line 9
  </code></pre>
  
  <p>I really can't find a way to get this working and I hope someone sees what I'm doing wrong. Thank you very much in advance!</p>