Is it possible in Mysql to update column information in several duplicated rows, leaving only one row with the original value?

Question

For example, I have several rows in a table (which I pulled from an XML of google products, so the same product is presented several times, each for a size of clothing/product) which are referenced for the same item, only with difference of product size, quantity and SKU code. In this table, I have a column that determines whether or not the product is presented in the system (0 or 1). What I would like to do is run a query that would check duplicate items, changing the parameter of this column that provides the preview, hiding all duplicate items, leaving only one with the original value.

I managed to achieve something similar with the query below:

DELETE a FROM sis_produto_xml a LEFT JOIN ( SELECT MIN(ID) id, sku, nome FROM sis_produto_xml GROUP BY nome) b ON a.id = b.id AND a.sku = b.sku AND a.nome = b.nome WHERE b.id IS NULL;

The problem of this query is that it deletes the product, and then XML downloads the same product again, that is, it is an endless job. Hiding the product through the table parameter (with an update instead of delete) this would not occur.

Thank you guys.

Answer 1

I realized that the post query deletes unique items, but I managed to solve the problem as follows:

DELETE table FROM table INNER JOIN ( SELECT MAX(id) AS lastid, column FROM table WHERE column IN ( SELECT column FROM table GROUP BY nome HAVING COUNT(*) > 1 ) GROUP BY column ) duplic ON duplic.column = table.column WHERE table.column2 < duplic.lastid;

Worth community!

Answer 2

You just change the query that you had quoted to UPDATE:

UPDATE a
   SET apresentado = 0
  FROM sis_produto_xml a
       LEFT JOIN (SELECT MIN(ID) id,
                         sku,
                         nome
                    FROM sis_produto_xml
                   GROUP BY nome) b ON a.id = b.id
                                   AND a.sku = b.sku
                                   AND a.nome = b.nome
 WHERE b.id IS NULL;