Db2 11.1

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

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