By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1 (ID int, Acreage decimal(10,2), Fvalue decimal(8, 4), Type int);
insert into table1 values
(1,16.24,null,1),
(2,2.17,null,1),
(3,138.00,null,3),
(4,138.00,null,1),
(5,142.47,null,3),
(6,6.16,null,2),
(7,14.80,null,2),
(8,26.01,null,1),
(9,26.01,null,3),
(10,1.45,null,3)
;
create table table2 (Acreage int, Factor decimal(8, 4), Type int);
insert into table2 values
(0,3.35,1),
(1,3.35,1),
(3,2.3,1),
(5,1.92,1),
(10,1.42,1),
(15,1.2,1),
(20,1,1),
(999,1,1),
(0,2.22,2),
(1,2.22,2),
(3,1.97,2),
(5,1.76,2),
(10,1.55,2),
(15,1.32,2),
(20,1.07,2),
(22,1,2),
(999,1,2),
(0,6.93,3),
(1,6.93,3),
(3,5.39,3),
select * from table2;
| Acreage | Factor | Type |
|---|---|---|
| 0 | 3.3500 | 1 |
| 1 | 3.3500 | 1 |
| 3 | 2.3000 | 1 |
| 5 | 1.9200 | 1 |
| 10 | 1.4200 | 1 |
| 15 | 1.2000 | 1 |
| 20 | 1.0000 | 1 |
| 999 | 1.0000 | 1 |
| 0 | 2.2200 | 2 |
| 1 | 2.2200 | 2 |
| 3 | 1.9700 | 2 |
| 5 | 1.7600 | 2 |
| 10 | 1.5500 | 2 |
| 15 | 1.3200 | 2 |
| 20 | 1.0700 | 2 |
| 22 | 1.0000 | 2 |
| 999 | 1.0000 | 2 |
| 0 | 6.9300 | 3 |
| 1 | 6.9300 | 3 |
| 3 | 5.3900 | 3 |
| 5 | 4.0500 | 3 |
| 10 | 2.5100 | 3 |
| 15 | 2.0800 | 3 |
| 20 | 1.6900 | 3 |
| 25 | 1.3100 | 3 |
| 30 | 1.0000 | 3 |
| 999 | 1.0000 | 3 |
update table1 y
join (
select
a.id,
case when lo.type is not null and hi.type is not null
then lo.factor + (a.acreage - lo.acreage)
/ (hi.acreage - lo.acreage)
* (hi.factor - lo.factor)
end as fvalue
from table1 a,
lateral (
select *
from table2 b
where b.type = a.type and b.acreage <= a.acreage
order by b.acreage desc
limit 1
) lo,
lateral (
select *
from table2 b
where b.type = a.type and b.acreage > a.acreage
order by b.acreage
limit 1
) hi
) x on x.id = y.id
set y.fvalue = x.fvalue
select * from table1;
| ID | Acreage | Fvalue | Type |
|---|---|---|---|
| 1 | 16.24 | 1.1504 | 1 |
| 2 | 2.17 | 2.7358 | 1 |
| 3 | 138.00 | 1.0000 | 3 |
| 4 | 138.00 | 1.0000 | 1 |
| 5 | 142.47 | 1.0000 | 3 |
| 6 | 6.16 | 1.7113 | 2 |
| 7 | 14.80 | 1.3292 | 2 |
| 8 | 26.01 | 1.0000 | 1 |
| 9 | 26.01 | 1.2474 | 3 |
| 10 | 1.45 | 6.5835 | 3 |