Netezza - update one table with max data from another table

I have a table in netezza that I need to update. The columns I am working with are

TABLE A

ID_NO
ENTRY_DATE
PRICE

TABLE B

ID_NO
START_DATE
END_DATE
PRICE

So an example of the data would look like this:

TABLE A

ID_NO	ENTRY_DATE	PRICE
123	2020-05-01
123	2020-08-15

TABLE B

ID_NO	START_DATE	END_DATE	PRICE
123	2019-01-01	2019-11-01	$7.64
123	2020-04-30	2020-05-02	$6.19
123	2020-04-15	2020-08-30	$2.19

I need to update the PRICE in TABLE A to be the max PRICE from TABLE B where a.ENTRY_DATE is between b.START_DATE and b.END_DATE. So the final table should look like this:

TABLE A

ID_NO	ENTRY_DATE	PRICE
123	2020-05-01	$6.19
123	2020-08-15	$2.19

This is what I have so far, but it just ends up taking the max price that fits either row rather than doing the calculation for each row:

update TABLE_A
set PRICE=(select max(b.PRICE) 
            from TABLE_B b
            inner join TABLE_A a on a.ID_NO=b.ID_NO
            where a.ENTRY_DATE between b.START_DATE and b.END_DATE)

Solution

I don't have access to Netezza, but a usual format would be to use a correlated sub-query.

That is, instead of including TABLE_A again in the query, you refer to the outer reference to TABLE_A...

update
  TABLE_A
set
  PRICE = (
    select max(b.PRICE) 
      from TABLE_B b
     where TABLE_A.ID_NO         =    b.ID_NO
       and TABLE_A.ENTRY_DATE between b.START_DATE and b.END_DATE
  )

In this way, the correlated-sub-query is essentially invoked once for each row in TABLE_A and that invocation uses the current row from TABLE_A as its parameters.

An alternative could be...

update
  TABLE_A
set
  PRICE = revised.PRICE
from
(
      select a.ID_NO, a.ENTRY_DATE, max(b.PRICE) AS PRICE
        from TABLE_B b
  inner join TABLE_A a on a.ID_NO=b.ID_NO
       where a.ENTRY_DATE between b.START_DATE and b.END_DATE
    group by a.ID_NO, a.ENTRY_DATE
)
  AS revised
where
      TABLE_A.ID_NO      = revised.ID_NO
  and TABLE_A.ENTRY_DATE = revised.ENTRY_DATE