title: Make UPDATE with JOIN apply to all records
body: <p>I have 3 tables:</p>
  
  <pre><code>TABLE bon                       TABLE order         TABLE payed 
  b_id | total | payed | diff     id | b_id | amm     id | b_id | amm 
  -----+-------+-------+------    ---+------+-----    ---+------+-----
    1  |   0   |   0   |  0        1 |   1  |   5      1 |   2  | -10
  -----+-------+-------+------    ---+------+-----    ---+------+-----
    2  |   0   |   0   |  0        2 |   2  |   5      2 |   3  |  -5  
  -----+-------+-------+------    ---+------+-----    ---+------+-----
    3  |   0   |   0   |  0        3 |   1  |  15
                                  ---+------+-----
                                   4 |   3  |  10
                                  ---+------+-----
                                   5 |   2  |  15
                                  ---+------+-----
                                   6 |   2  |  20
  </code></pre>
  
  <p>If I want to update the totals in <strong>bon</strong> for only one id, I do:</p>
  
  <pre><code>UPDATE `bon`
      INNER JOIN (
          SELECT
          SUM(`amm`) AS `total`
          FROM `order` 
          WHERE `b_id`=2
      ) AS s1
  
      INNER JOIN (
          SELECT
          COALESCE(SUM(`amm`),0) AS `payed`
          FROM `payed` 
          WHERE `b_id`=2
      ) AS s2
  
      SET
      `bon`.`total` = s1.`total`,
      `bon`.`payed` = s2.`payed`,
      `bon`.`diff` = s1.`total` + s2.`payed`
  WHERE `b_id`=2;
  </code></pre>
  
  <p>That updates <strong>id 2</strong> correctly to</p>
  
  <pre><code>b_id | total | payed | diff   
  -----+-------+-------+------
    2  |   40  |  -10  |  30
  </code></pre>
  
  <p>However, I try to update <strong>ALL</strong> id's with: </p>
  
  <pre><code>UPDATE `bon`
      INNER JOIN (
          SELECT
          `b_id`,
          SUM(`amm`) AS `total`
          FROM `order` 
      ) AS s1 ON bon.`b_id`=s1.`b_id`
  
      INNER JOIN (
          SELECT
          `b_id`,
          SUM(`amm`) AS `payed`
          FROM `payed` 
      ) AS s2 ON bon.`b_id`=s2.`b_id`
  
      SET
      `bon`.`total` = s1.`total`,
      `bon`.`payed` = COALESCE(s2.`payed`,0),
      `bon`.`diff` = s1.`total` + COALESCE(s2.`payed`,0)
  </code></pre>
  
  <p>Sadly, this updates no rows. So where am I going wrong in this one?</p>
  
  <p><a href="http://sqlfiddle.com/#!9/f6689c/1" rel="nofollow noreferrer">Here's a fiddle</a></p>
  
  <p><strong>EDIT:</strong> I have found a solution and posted it below. Don't know if it is the best way to do this, but it works (suggestions still welcome).</p>