Bulk update using multiple joins in postgres

Question

Hope you all are doing good!

I am trying to run mass update on table using multiple joins but I am unable to do so.

I have 3 tables:

table 1: items

|id|total_price|created_at|

table 2: prices

|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|

table 3: ranges

|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        |

END RESULT I AM TRYING TO ACHIEVE: I would like to mass update prices table based on below SQL:

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

The result would be like below:

|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|

So far the limitation I am running into:

1. It always runs for the first range defined and never executed for the 2nd range defined.
2. I tried using various ways to do that but none was giving satisfactory results.

IMP I will be doing this for millions of records in prices table

Please help/advise!

Answer 1

I was able to resolve this by below SQL:

UPDATE prices
    SET special_price = ROUND((special_price + (special_price * ranges.commission_percent / 100)), 2)
  FROM items, ranges
  WHERE (prices.special_price BETWEEN ranges.range_from AND ranges.range_to)
    AND prices.company_id = ranges.company_id
    AND prices.stock_item_id = items.id AND prices.special_price = items.total_price
    AND prices.company_id = 'XX';

The advantage of using this SQL is:

1. It will only update special_price for which ranges is/are changed.
2. Super fast response time
3. Can be run independently and safe to plugged-in (i.e. whenever ranges are inserted/deleted)