StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>It seems like your subquery should be grouping on <code>field6</code> and exposing that column for inner join with <code>table1</code>. Here's how you do that <a href="http://dev.mysql.com/doc/refman/5.6/en/update.html" rel="noreferrer" title="UPDATE Syntax">in MySQL</a>:</p> <pre><code>UPDATE table1 W INNER JOIN ( SELECT B.field6, SUM(A.field2) AS field2 FROM table2 A, table3 B, table4 P WHERE A.id=B.id AND P.field6=B.field6 GROUP BY B.field6 ) B ON W.field6=B.field6 SET W.Field1 = B.Field2 </code></pre> <p>And while we are at it, I would also recommend you to refrain from (ab)using comma joins in favour of explicit joins. The latter, however unusual at first after being long accustomed to a different syntax, can very soon become habitual and much more intuitive than the former. <a href="http://www.google.com/search?q=comma+join+vs+explicit+join" rel="noreferrer">A great deal</a> has been said on the topic, and some people may be holding quite strong opinions about comma joins. I say, comma joins can still have their share of use. However, when you are joining on a condition, the current ANSI syntax should be your choice.</p> <p>Here's the above statement with the subquery transformed so as to use explicit joins:</p> <pre><code>UPDATE table1 W INNER JOIN ( SELECT B.field6, SUM(A.field2) AS field2 FROM table2 A INNER JOIN table3 B ON A.id = B.id INNER JOIN table4 P ON P.field6 = B.field6 GROUP BY B.field6 ) B ON W.field6 = B.field6 SET W.Field1 = B.Field2 </code></pre>

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