By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1 (
column1 int,
column2 int,
column3 int,
amount int
);
insert into table1 values
(1, 2, 3, 10),
(1, 2, 3, 10),
(2, 2, 3, 10),
(2, 2, 3, 10),
(2, 2, 3, 10);
create table table2 (
column1 int,
column2 int,
column3 int,
amount int,
UNIQUE KEY `column1` (`column1`) USING BTREE
);
INSERT INTO table2 (column1,column2, column3, amount) values
(1, 2, 3, 0);
Records: 5 Duplicates: 0 Warnings: 0
INSERT INTO table2 (column1,column2, column3, amount)
SELECT *
FROM (
SELECT column1, column2, column3, SUM(amount) AS _amount
FROM table1
GROUP BY column1, column2, column3
) AS S
ON DUPLICATE KEY UPDATE amount = _amount;
SELECT *
FROM table2;
Records: 2 Duplicates: 1 Warnings: 0
| column1 | column2 | column3 | amount |
|---|---|---|---|
| 1 | 2 | 3 | 20 |
| 2 | 2 | 3 | 30 |