title: Update with nested subquery (sum) to get restricton on update clause
body: <p>Got this bit of SQL as an update script, I've tried to add a work round to not being able to include the table to be updated as a clause in the statement so using sub queries, but  struggling to get this to work.</p>
  
  <p>Essientially I need update a vailue in table 1 with the summation of a field in table 2, but only where the two other fields match across a couple of tables and based on field6 the restriction is applied to the update clause.</p>
  
  <pre><code>     UPDATE table1 W SET Field1=(SELECT field2 FROM
       (SELECT A.id, B.field3, SUM(A.field2) AS field2 
       FROM table2 A, table3 B, table4 P
       WHERE A.id=B.id AND P.field6=B.field6) B ) WHERE W.field6=B.field6
  </code></pre>
  
  <p>In the real world example, select the sum of points conceded in a rugby game when a rugby player has participated in the match. table 2 has the results (including the score) table 3 has the team sheets and table 1 and 4 are the same player table to be updated.</p>
  
  <p>Hopefully this is clear enough and someone can point me in the right direction.</p>
  
  <p>Tried the following:</p>
  
  <pre><code>     UPDATE $WSLKEEP W, $WSLFIX A, $WSLFIXPLAY B 
       SET W.F_CONCEDED=SUM(A.F_AGAINST) 
       WHERE A.F_ID=B.F_GAMEID 
       AND B.F_NAME=W.F_NAME"
  </code></pre>
  
  <p>but now stuck with:
           Invalid use of group function</p>
  
  <p>Kind regards</p>