SQL - Delete records that have field with the same value as another, only when there is more than one record

Question

SQL - Delete records that have field with the same value as another, only when there is more than one record

Navigation

#1 by (2 votes)

0

I have a barcode table of products, I want to delete only the barcodes that are equal to the product code and have another code besides this. Table:

CodProduto    CodBarra
    01           01      < não deletar
    02           02      < deletar
    02         789123    < não deletar
    02         789124    < não deletar
    03           03      < não deletar
    04           04      < deletar
    04         789125    < não deletar
    04         789126    < não deletar

I've tried it a couple of ways, but I always delete all bar codes that are the same as the product code. Do you have an idea or do you know if this is possible or not?

mysql sql firebird

asked by anonymous 16.08.2017 / 18:13

1 answer

Delete delete button does not work Registration - comparison of existing or non-existent values

score 2 · Accepted Answer

You can use the WHERE clause normally to check if the code is the same as the bar code and then check if there is another record with the reverse condition:

DELETE t1
  FROM tabela t1
 WHERE t1.CodProduto = t1.CodBarra
   AND EXISTS(SELECT 1
                FROM tabela t2
               WHERE t2.CodProduto = t1.CodProduto
                 AND t2.CodProduto <> t2.CodBarra)