MySQL 8.0

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

CREATE TABLE tablename (
  `sid` INTEGER,
  `id` INTEGER,
  `amount` INTEGER
);

INSERT INTO tablename
  (`sid`, `id`, `amount`)
VALUES
  ('1', '12', '30'),
  ('2', '45', '30'),
  ('3', '45', '50'),
  ('4', '78', '80'),
  ('5', '78', '70');
UPDATE tablename t1
INNER JOIN (
  SELECT MIN(sid) sid, id
  FROM tablename
  GROUP BY id
) t2 ON t2.id = t1.id AND t2.sid < t1.sid
INNER JOIN tablename t3 ON t3.sid = t2.sid
SET t1.amount = t3.amount;
SELECT * FROM tablename;


sid
id
amount


1
12
30

2
45
30

3
45
30

4
78
80

5
78
80