CODEWITHSUNDEEP
mysqlgroup-by
Haim Evgi
Haim Evgi

Reputation: 125496

Delete duplicate rows and add the deleted rows values to one that remain

I have a table like :

id      |    name    |  profit |  cost  
---------------------------------------
1       |  aaa       | 4       | 2 
2       |  aaa       | 4       | 3 
3       |  aaa       | 4       | 2 
4       |  bbb       | 4       | 1

I want to delete from this table duplicate rows (according the name) but before do the delete add the value of the deleted rows to the remain row

so in this case I want that the table after run queries look like :

id      |    name    |  profit |  cost  
---------------------------------------
1       |  aaa       | 12      | 7 
4       |  bbb       | 4       | 1

Is it possible to do it in mysql, without create another table and copy the data, because this is a big table (1 million rows but increase every day) ?

Upvotes: 6

Views: 139

Answers (1)

valex
valex

Reputation: 24144

SQLFiddle demo

First update rows with min(id) for each NAME 

UPDATE T a JOIN 
(
  SELECT min(ID) as minID,name,SUM(profit) as SP,SUM(cost) as SC 
  FROM T GROUP BY name
) b 
 ON a.id = b.minID 
 SET a.profit = b.sp,a.cost=b.sc;

And then delete rows except only those rows with min(id) for each NAME

DELETE T
FROM T
LEFT JOIN 
(
  SELECT min(ID) minid ,name FROM T GROUP BY name
) b  
ON t.id = b.minid
WHERE b.minid is NULL

Upvotes: 1

Related Questions