SQL DB2: How to update field in one table to max value of another table?

Question

I have two tables, one with units and mileage (unit field is unique: one row per unit):

Table A (unit master)

unit	mileage
1	0
2	0
3	0

And another table with units and mileage (multiple rows per unit):

Table B (unit history)

unit	mileage
1	100
1	140
2	102
2	150
3	107
3	129

I am trying to construct a sql to populate the A.mileage field with the MAX B.mileage for each unit.

update 
A
join B
on A.unit = B.unit
join ( 
  select max(mileage) as max_mil, unit
  from B
  group by unit
)maxTable 
on maxTable.max_mil = B.mileage
and maxTable.unit = B.unit
set A.mileage = B.mileage;

Edit: To clarify, I got a 104 error (originally I made a typo and said 107). A 104 is just an 'invalid token' it looks like it had an issue with the first join and was expecting a 'set' clause after the update line. Another comment asked me to define my question a little better. Unfortunately, I realize it is a little vague. My question is the following: how to I modify/rewrite the above sql statement so I can accomplish my goal as described above.

Answer 1

Try with a merge statement :

merge into a using (
  select unit, max(mileage) max_mileage from b group by unit
) as maxb on a.unit = maxb.unit
when matched and a.mileage is distinct from maxb.max_mileage
  then update set a.mileage = maxb.max_mileage

Answer 2

Db2 for LUW supports the "UPDATE FROM SELECT" syntax.

CREATE TABLE A (unit, mileage) AS
(
VALUES
  (1, 0)
, (2, 0)
, (3, 0)
) WITH DATA

CREATE TABLE B (unit, mileage) AS
(
VALUES
  (1, 100)
, (1, 140)
, (2, 102)
, (2, 150)
, (3, 107)
, (3, 129)
) WITH DATA
;

UPDATE A
SET MILEAGE = B.MILEAGE
FROM
(
  SELECT UNIT, MAX (MILEAGE) AS MILEAGE
  FROM B
  GROUP BY UNIT
) B
WHERE A.UNIT = B.UNIT
;

SELECT * FROM A;

UNIT	MILEAGE
1	140
2	150
3	129

fiddle