MySQL 8.0

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

Records: 10  Duplicates: 0  Warnings: 0



Records: 27  Duplicates: 0  Warnings: 0



Rows matched: 10  Changed: 10  Warnings: 4


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


select 
  a.id,
  a.acreage,
  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,
  a.type
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


id
acreage
fvalue
type


1
16.24
1.1504000000
1

2
2.17
2.7357500000
1

3
138.00
1.0000000000
3

4
138.00
1.0000000000
1

5
142.47
1.0000000000
3

6
6.16
1.7112800000
2

7
14.80
1.3292000000
2

8
26.01
1.0000000000
1

9
26.01
1.2473800000
3

10
1.45
6.5835000000
3

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

id	acreage	fvalue	type
1	16.24	1.1504000000	1
2	2.17	2.7357500000	1
3	138.00	1.0000000000	3
4	138.00	1.0000000000	1
5	142.47	1.0000000000	3
6	6.16	1.7112800000	2
7	14.80	1.3292000000	2
8	26.01	1.0000000000	1
9	26.01	1.2473800000	3
10	1.45	6.5835000000	3