StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUpdating multiple columns with data from subquery in MySQL
text
Body
copied!<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> 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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload