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 |