Compare a field in the same table

Asked

Viewed 240 times

-4

I need to double-check the register with the same CNPJ to eliminate duplicates. select needs to bring the code information,duplicate clients name.

Bank structure:

tabela CADCLI
( 
  codigo, 
  nome,cnpj
)

BD used: Firebird

I tried that select:

SELECT CODIGO,
       RAZAO,
       CNPJ
  FROM CADCLI WHERE CNPJ IN (
    SELECT CNPJ
      FROM CADCLI
      GROUP BY CNPJ
     HAVING COUNT(*) > 1)
 ORDER BY CNPJ

But it hangs not and does not bring the result.

sql firebird

  • It would be nice if you provide the structure of the tables and what you’ve tried so far so we can help you

  • Use Cunt() to count CNPJS, group by and dps using having Count() > 0

  • cadcli table, CNPJ field.

  • Related: How to delete all duplicates except one?

  • I recommend editing the question and putting the type of bank not to confuse who will answer.

2 answers

2

Using GROUP BY to group by CPF and HAVING COUNT where the number of equal CPF is greater than 1:

SELECT ID,Nome FROM Usuarios GROUP BY CPF Having COUNT(CPF) > 1;

Then you get the ID’s and delete.

  • returned this error: Invalid Expression in the select list (not contained in either an Aggregate Function or the GROUP BY clause).

  • Which SQL you use?

  • I use the ibexpert, bd Firebird

  • I thought it was Mysql, put the database type in the question tag.

  • blz friend, thanks for the help.

1

So you will bring all the codes you must delete:

SELECT c1.*
  FROM CADCLI c1,
       CADCLI c2
 WHERE c1.CODIGO > c2.CODIGO
   AND c1.CNPJ = c2.CNPJ;

Browser other questions tagged sql firebird

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