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');

Records: 5  Duplicates: 0  Warnings: 0


UPDATE tablename t1
INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY sid) rn
  FROM tablename
) t2 ON t2.id = t1.id  
SET t1.amount = t2.amount
WHERE t2.rn = 1;

Rows matched: 5  Changed: 2  Warnings: 0


SELECT * FROM tablename;


sid
id
amount


1
12
30

2
45
30

3
45
30

4
78
80

5
78
80