title: Advanced SQLite Update table query
body: <p>I am trying to update Table B of a database looking like this:</p>
  
  <pre><code>Table A:
  id, amount, date, b_id
  1,200,6/31/2012,1
  2,300,6/31/2012,1
  3,400,6/29/2012,2
  4,200,6/31/2012,1
  5,200,6/31/2012,2
  6,200,6/31/2012,1
  7,200,6/31/2012,2
  8,200,6/31/2012,2
  
  Table B:
  id, b_amount, b_date
  1,0,0
  2,0,0
  3,0,0
  </code></pre>
  
  <p>Now with this query I get all the data I need in one select:</p>
  
  <pre><code>SELECT A.*,B.* FROM A LEFT JOIN B ON B.id=A.b_id WHERE A.b_id&gt;0 GROUP BY B.id
  
  id, amount, date, b_id, id, b_amount, b_date
  1,200,6/31/2012,1,1,0,0
  3,400,6/29/2012,1,1,0,0
  </code></pre>
  
  <p>Now, I just want to copy the selected column amount to b_amount and date to b_date </p>
  
  <pre><code>b_amount=amount, b_date=date
  </code></pre>
  
  <p>resulting in</p>
  
  <pre><code>id, amount, date, b_id, id, b_amount, b_date
  1,200,6/31/2012,1,1,200,6/31/2012
  3,400,6/29/2012,1,1,400,6/29/2012
  </code></pre>
  
  <p>I've tried COALESCE() without success.
  Does someone experienced have a solution for this?</p>
  
  <h2>Solution:</h2>
  
  <p>Thanks to the answers below, I managed to come up with this. It is probably not the most efficient way but it is fine for a one time only update. This will insert for you the first corresponding entry of each group.</p>
  
  <pre><code>REPLACE INTO A SELECT id, amount, date FROM 
  (SELECT  A.id, A.amount, B.id as Bid FROM A INNER JOIN B ON (B.id=A.B_id)
  ORDER BY A.id DESC) 
  GROUP BY Bid;
  </code></pre>