lookup a table with range to populate an attribute

Question

I have a requirement where I have two tables as below:

table named T1:

+-----------+
| days rate |
+-----------+
|         2 |
|        12 |
+-----------+

and table named T2:

+-------------+-------------+------+
| lower_range | upper_range | rate |
+-------------+-------------+------+
|           0 |           5 |    1 |
|          10 |          15 | 0.75 |
+-------------+-------------+------+

I would like to update "rate" on T1 by referring to T2 using days by determining which bucket it falls to so the expected output would be:

+------+------+
| days | rate |
+------+------+
|    2 |    1 |
|   12 | 0.75 |
+------+------+

Answer 1

You can use a correlated subquery:

update t1
    set rate = (select t2.rate from t2 where t1.days >= t2.lower_range and t1.days <= t2.upper_range);

If you only want to set values that match, then use where as well:

update t1
    set rate = (select t2.rate from t2 where t1.days >= t2.lower_range and t1.days <= t2.upper_range)
    where exists (select 1 from t2 where t1.days >= t2.lower_range and t1.days <= t2.upper_range);