Is it possible, in MySQL, to update column information on multiple duplicate rows, leaving only one row with the original value?

Question

Is it possible, in MySQL, to update column information on multiple duplicate rows, leaving only one row with the original value?

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

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 clothing / product size) that are referenced for the same item, only with product size difference, quantity and SKU code. In this table, I have a column that determines whether or not the product is displayed in the system (0 or 1). What I would like to do is to run a query that would check the duplicate items, changing the parameter of that column that makes the visualization available, hiding all duplicate items, leaving only one with the original value.

I was able 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 with this query is that it deletes the product, and then XML drops the same product again, that is, it is a work that has no end. Hiding the product through the table parameter (with an update instead of delete) would not happen.

Thank you guys.

php mysql sql

asked by anonymous 02.12.2016 / 19:11

2 answers

0

It's only up to you to 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;

02.12.2016 / 19:43

How to get DB value for a variable? Checkbox with function

score 1 · Accepted Answer

I noticed that the post query deletes unique items, but I was able to solve the problem in the following way:

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;

Thanks for your help!