title: Mysql, update query
body: <p>I have table like this:</p>
  
  <p>table1</p>
  
  <pre><code>first_id    second_id    value
   1           0            10
   2           0            60 &lt;- can be bad value, need update this
   2           12           30
   2           14           30
   3           0            50
   4           0            100 &lt;- can be bad value, need update this
   4           20           50
   4           41           30
   4           33           20
  </code></pre>
  
  <p>I need update rows that have second_id = 0 and in table exists rows with same first_id but second_id != 0. I need update this rows with sum of rows that have same first_id and second_id != 0.</p>
  
  <p>For example:</p>
  
  <pre><code>first_id = 3 and second_id = 0 =&gt; not update, 0 rows with first_id = 3 and second_id != 0
  
  first_id = 4 and second_id = 0 =&gt; update, SUM(50,30,20) = rows with same first_id and second_id != 0
  </code></pre>
  
  <p>How can I do this in one update statement?</p>
  
  <p>I tried sth like this but without effect (problem with recursive query?).</p>
  
  <pre><code>UPDATE table1 t1 SET t1.value = 
  ( 
      SELECT SUM(t2.value) 
      FROM table1 t2 
      WHERE t2.second_id != 0 AND t2.first_id = t1.first_id
  )
  WHERE t1.second_id = 0 AND 
  (
      SELECT COUNT(*) 
      FROM table1 t3 
      WHERE t3.first_id = t1.first_id
  ) &gt; 1
  </code></pre>