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

Question

I have a product bar code table, I want to delete only those bar codes that are the same as the product code and that have another code besides this one. 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 tried in some ways, but I always end up deleting all barcodes that match the product code. Do you have an idea or do you know whether this is possible or not?

Answer 1

You can use the clause WHERE usually to check if the code is equal to the barcode 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)