title: Bulk update using multiple joins in postgres
body: <p>Hope you all are doing good!</p>
  
  <p>I am trying to run mass update on table using multiple joins but I am unable to do so.</p>
  
  <p>I have 3 tables:</p>
  
  <p>table 1: <code>items</code></p>
  
  <pre><code>|id|total_price|created_at|
  </code></pre>
  
  <p>table 2: <code>prices</code></p>
  
  <pre><code>|id|company_id|item_id|special_price|created_at|
  ________________________________________________
  | 1|    1     | 100   |     20.0    | 2017-01-01|
  | 2|    1     | 101   |     30.0    | 2017-01-01|
  | 3|    1     | 102   |     70.0    | 2017-01-01|
  | 4|    1     | 103   |     90.0    | 2017-01-01|
  </code></pre>
  
  <p>table 3: <code>ranges</code></p>
  
  <pre><code>|id|company_id|range_from|range_to|commission_percent|
  ______________________________________________________
  | 1|    1     |  10.0    |  50.0  |       5.0        |
  | 2|    1     |  51.0    | 100.0  |      10.0        |
  </code></pre>
  
  <p>END RESULT I AM TRYING TO ACHIEVE: I would like to mass update <code>prices</code> table based on below SQL:</p>
  
  <pre><code>UPDATE prices
    SET special_price = (
      CASE
      WHEN (special_price BETWEEN ranges.range_from AND ranges.range_to) AND prices.company_id = ranges.company_id
        THEN
          ROUND((special_price + (special_price * commission_percent / 100)), 2)
      ELSE
        special_price
      END
    )
    FROM ranges
    WHERE prices.company_id = 1
  </code></pre>
  
  <p>The result would be like below:</p>
  
  <pre><code>|id|company_id|item_id|special_price|created_at|
  ________________________________________________
  | 1|    1     | 100   |     21.0    | 2017-01-01|
  | 2|    1     | 101   |     31.5    | 2017-01-01|
  | 3|    1     | 102   |     77.0    | 2017-01-01|
  | 4|    1     | 103   |     99.0    | 2017-01-01|
  </code></pre>
  
  <p>So far the limitation I am running into:</p>
  
  <ol>
  <li>It always runs for the first range defined and never executed for the 2nd range defined.</li>
  <li>I tried using various ways to do that but none was giving satisfactory results.</li>
  </ol>
  
  <p><em>IMP</em> I will be doing this for millions of records in <code>prices</code> table</p>
  
  <p>Please help/advise!</p>