Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table a as
    select 1 as id, 0   as depth, 0 as temperature union all
    select 2 as id, 15  as depth, 24.8 as temperature union all
    select 3 as id, 30  as depth, 25.1 as temperature union all
    select 4 as id, 45  as depth, 33.4 as temperature union all
    select 5 as id, 50  as depth, 35 as temperature union all
    select 6 as id, 60  as depth, 36.3 as temperature union all
    select 7 as id, 80  as depth, 40.2 as temperature union all
    select 8 as id, 100 as depth, 60.1 as temperature 
8 rows affected

create table b as
    select 1 as id, 0 as depth, null as temperature union all
    select 2 as id, 30 as depth, null as temperature union all
    select 3 as id, 40 as depth, null as temperature union all
    select 4 as id, 50 as depth, null as temperature union all
    select 5 as id, 60 as depth, null as temperature union all
    select 6 as id, 75 as depth, null as temperature union all
    select 7 as id, 80 as depth, null as temperature union all
    select 8 as id, 100 as depth, null as temperature 
8 rows affected

select b.*, a.temperature
from b left join lateral
     (select a.*
      from a
      where a.depth <= b.depth
      order by a.depth desc
      limit 1
     ) a
     on 1=1


id
depth
temperature
temperature


1
0
null
0

2
30
null
25.1

3
40
null
25.1

4
50
null
35

5
60
null
36.3

6
75
null
36.3

7
80
null
40.2

8
100
null
60.1


update b
    set temperature = (select a.temperature
                       from a
                       where a.depth <= b.depth
                       order by a.depth desc
                       limit 1
                      )
8 rows affected

select *
from b


id
depth
temperature


1
0
0

2
30
25.1

3
40
25.1

4
50
35

5
60
36.3

6
75
36.3

7
80
40.2

8
100
60.1

id	depth	temperature	temperature
1	0	null	0
2	30	null	25.1
3	40	null	25.1
4	50	null	35
5	60	null	36.3
6	75	null	36.3
7	80	null	40.2
8	100	null	60.1