PostgreSQL Update one table data from another

Question

I have two tables:

Table A:

id  depth   temperature
1   0       0
2   15      24.8
3   30      25.1
4   45      33.4
5   50      35
6   60      36.3
7   80      40.2
8   100     60.1

Table B:

id  depth   temperature
1   0
2   30
3   40
4   50
5   60
6   75
7   80
8   100

In result i need table B like this with temperature column updated

id  depth   temperature
1   0       0         temperature 0 from table A because there is the same depth(0), and we get 0
2   30      25.1      temperature 25.1 from table A because there is the same depth(30), and we get 25.1
3   40      25.1      temperature 25.1 from table A because there is no 40 depth, and in this case we get temperature from measurement one above (depth 40), from depth 30 
4   50      35        temperature 35 from table A because there is the same depth (50), and we get 35
5   60      36.3      temperature 36.3 from table A because there is the same depth (60), and we get 36,3
6   75      36.3      temperature 36,3 from table A because there is no 75 depth, and in this case we get temperature from measurement one above (depth 60), from depth 60
7   80      40.2      temperature 40.2 from table A because there is the same depth (80), and we get 35
8   100     60.1      temperature 60.1 from table A because there is the same depth (100), and we get 35

What query can update table B like shown above?

PostgreSQL 11

Regards

Answer 1

I think you just want a lateral join:

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;

Or if you want an update use a correlated subquery:

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

Here is a db<>fiddle.