title: MySQL: update with join using GROUP_CONCAT: ERROR 1111 (HY000): Invalid use of group function
body: <p>so I am updating a table that's functionally equivalent to:</p>
  
  <pre><code>create table NODE(
     group_name varchar(100) not null,
     full_name varchar(100) not null,
     dependency longtext,
     is_top ENUM('0','1') not null default 0,
     ...
  );
  </code></pre>
  
  <p>The dependency is a concatenation of the <code>full_name</code> of all the other nodes that get triggered when something happens to this node that needs to trickle down to the ancestor nodes. There is a top level node for which all are 'children' of. Occasionally a bunch more get added to the group and I need to update the <code>dependency</code> of the top level node with <code>full_names</code> of the other child nodes as one giant string separated by '&amp;'.</p>
  
  <p>Here was my query:</p>
  
  <pre><code>update NODE n1 
  join NODE n2 
  on n2.group_name=n1.group_name 
  set n1.dependency=GROUP_CONCAT(n2.full_name SEPARATOR '&amp;') 
  where n1.is_top='1' and n2.is_top='0' and n1.group_name='MY GROUP NAME';
  </code></pre>
  
  <p>error:</p>
  
  <pre><code>ERROR 1111 (HY000): Invalid use of group function
  </code></pre>
  
  <p>My understanding is this should be fine, after all you can use <code>GROUP_CONCAT</code> in a select statement, and I do it all the time, I don't understand how this is invalid.</p>
  
  <p>for the curious: I did not design this database, and yes I re-named things to hide the true nature of the database and how it works.</p>