Check duplicate items and update a column in all but one

Asked

Viewed 521 times

2

I have a table of products that bring information from an XML, and another table in the system that basically receives these products after approval (we have an interface to define what enters or not, basically changing a value in the product so that it is not visible but remains at the base). Products that enter XML often bring several similar items, change only a few parameters in the product SKU, due to difference in size of clothes, as the example:

inserir a descrição da imagem aqui

What I’d like to do is this:

How can I select all duplicate results, except one of them, and update the imported column of these that select bring. The imported as 0 hides the product from the approval queue, so the queue will not show several times the same product that basically only has the different SKU.

Thank you guys.

php mysql sql sql-update

  • tried to use the <pre>UPDATE sis_prodo_xml query WHERE sku NOT IN (SELECT MAX(id) AS lastid, name FROM sis_producto_xml WHERE name IN ( SELECT name FROM sis_producto_xml GROUP BY name HAVING COUNT(*) > 1 ) GROUP BY name) SET imported = 1;</pre> but failed.

2 answers

0

I think a GROUP BY would work, something like:

UPDATE tabela SET importado = 1 WHERE sku <> FIRST(sku) GROUP BY nome

Only test with a SELECT first to see if you’re selecting the right records

SELECT * FROM tabela WHERE sku <> FIRST(sku) GROUP BY nome

  • I tested here but presented an error in <>, I’m taking a look

  • I tried it here but it didn’t work out no. thanks for the help.

0

The update below as long as it seems it is effective for the purpose questioned.

UPDATE
    demo
SET
    importado = 0
WHERE
    sku NOT IN -- Atualizará os demais sku das duplicações
(
  SELECT 
    Max(x.sku) -- Busco o maior SKU dentre os duplicados, assim selecionando somente um de cada duplicacao
  FROM 
    demo x 
  WHERE 
    EXISTS 
    (
      SELECT
        count(1)
      FROM
        demo y
      WHERE 
          x.name = y.name
      GROUP BY y.name
      HAVING COUNT(1) > 1
    )
  GROUP BY x.name
)
AND name IN -- Somente os nomes com duplicações serão atualizados
(
  SELECT
    x.name
  FROM
      demo x
  WHERE
    EXISTS
      (
      SELECT count(1) from demo y
      WHERE 
        x.name = y.name
      GROUP BY y.name
      HAVING COUNT(1) > 1
    )
)

Browser other questions tagged php mysql sql sql-update

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