Select command to delete duplicates by summing the results

Asked

Viewed 264 times

0

How to change a table that looks like this:

produto | quant a | 10 a | 5 b | 8 b | 3

And leave her like this:

produto | quant a | 15 b | 11

Is there any command select that I can use to make this modification?

mysql select

  • In the title you say delete, but in the body of the question you show the junction of the records. Got confused.

  • It would be very interesting to have your query, but in this case you should use the group_by column

  • believe it is duplicate too, the link that Sorack posted should solve your problem

  • @Andersoncarloswoss was even explanation error. The idea is to delete the names of the repeated products, but keeping the sum value of all of them

1 answer

2


If you want to delete the smallest amount:

DELETE p1
  FROM produto p1
       INNER JOIN produto p2 ON p1.produto = p2.produto
 WHERE p1.quant < p2.quant;

If you want to delete the highest amount:

DELETE p1
  FROM produto p1
       INNER JOIN produto p2 ON p1.produto = p2.produto
 WHERE p1.quant > p2.quant;

If you want to group the records would be as follows:

 SELECT p.produto,
        SUM(p.quant) AS quant
   FROM produto p
  GROUP BY p.produto;

If you want to group and then delete, do one solution after another:

 SELECT p.produto,
        SUM(p.quant) AS quant
   FROM produto p
  GROUP BY p.produto;

DELETE p1
  FROM produto p1
       INNER JOIN produto p2 ON p1.produto = p2.produto
 WHERE p1.quant < p2.quant;

Browser other questions tagged mysql select

You are not signed in. Login or sign up in order to post.