I want to sum the "amount" column grouped by name and server, and remove duplicate lines. I want those changes are saved (not only with one query).

For example, here is begin data:

Id server name amount
1 20 a 1
2 20 a 1
3 30 b 2
4 20 b 1
5 20 a 1

And here is what I want:

Id server name amount
1 20 a 3
3 30 b 2
4 20 b 1

Actually, I can do it with:

SELECT SUM(amount) as amount FROM myTable GROUP BY server, name;

But, I will only get the value for the amount column. It will not update the table and not remove other lines.

If I do something like UPDATE myTable SET amount = (SELECT SUM(amount) as amount FROM myTable WHERE server = myTable.server AND name = myTable.name) it will not works as I can't do a select into an update.

So, how can I do?


1 Answer 1


Basically you are asking to delete the records from your table I hope this is a single process.

This can be done by inserting your "final" data into a temporary table truncate your existing table and then insert the data from the temporary table to your main table.

As per the id it seems excess to me, you can remove it and leave it autoincrement.


SET autocommit=0;

create temporary table temp_myTable like myTable;
insert into temp_myTable SELECT min(id) as id,server,name,SUM(amount) as amount 
FROM myTable 
GROUP BY server, name;
truncate myTable;
insert into myTable select * from temp_myTable;
drop temporary table temp_myTable;



select * from myTable;

id  server  name    amount
1      20    a       3
3      30    b       2
4      20    b       1

See example

See example with new id-s

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.