title: Mysql update row value in table based on different row
body: <p>I have the following table structure</p>
  <pre><code>TABLE A   
      Productid    price   groupId  
       1             100      A
       2             99       A
       3              0       A
       4             50       B
       5             49       B
       6              0       B
  </code></pre>
  <p>I populate table A with prices from table B joining on Id. Sometimes table B doesn't have prices.
  In cases were b doesn't have price I want to update the price to be another price from that same group, as I can't have a price of zero.</p>
  <p>Is there an way to update table a price column using itself based on group? e.g. update productId 3 price to be the price of another product in it's group (1 or 2)</p>
  <pre><code>TABLE A after update  
  Productid    price   groupId  
   1             100      A
   2             99       A
   3             100      A
   4             50       B
   5             49       B
   6             49       B
  </code></pre>
  <p>It seems silly but these are the business rules (it makes sense irl I simplified the problem for the example)</p>
  <p>When  I tried the following I got Error:</p>
  <pre><code>    update 'Table A' t1
      join (select price ,groupId from 'table A' where Price &gt; 0 group by 
      groupId) as t2
      on t1.groupId = t2.GroupId
      SET t1.Price = t2.Price
    
       (conn=58292) Can't reopen table: 'Table A' 
  </code></pre>
  <p>I've thought of creating a third temporary table but that seems.... wrong? I am sure there must be a way to do this using update statement and joins</p>