Duplicate record - Firebird

Question

In Firebird how to delete duplicate records, keeping only one of them? Someone could help me. I have this select, but it deletes all records, use Firebird 2.1

        delete from vendaproduto where vendaproduto.pro_codigo in
        (select vendaproduto.pro_codigo from vendaproduto where
        VENDAPRODUTO.ven_codigo = 2432
        having COUNT(*) != 1)

Answer 1

There is more than one way to do this. Based on the SQL shown, it is possible to do this:

DELETE FROM vendaproduto 
        WHERE
          pro_codigo IN (
            SELECT a.pro_codigo FROM vendaproduto a 
              WHERE a.pro_codigo in ( 
                   SELECT b.pro_codigo FROM vendaproduto b 
                   WHERE a.pro_codigo = b.pro_codigo 
                   AND a.ven_codigo = b.ven_codigo 
                   AND b.ven_codigo = 2432 
                   GROUP BY b.pro_codigo 
                   HAVING COUNT(*) != 1 )
          )

Or of this, without HAVING:

DELETE FROM vendaproduto vp
        LEFT JOIN
        (
            SELECT MIN(pro_codigo) pro_codigo, ven_codigo
            FROM vendaproduto
            GROUP BY pro_codigo
        ) b ON vp.pro_codigo = b.pro_codigo AND
                vp.ven_codigo = b.ven_codigo
                AND b.ven_codigo = 2432 --se remover esta linha, ele busca e elimina todos os registros com pro_codigo e pro_codigo duplicados no banco de dados, mantendo o último registro
            WHERE b.pro_codigo IS NULL 

Or else this, which is my favorite, for being the simplest to understand:

DELETE
  FROM
    vendaproduto 
WHERE
  pro_codigo IN (
  SELECT
    a.pro_codigo
  FROM
    vendaproduto a
  WHERE
    a.pro_codigo < (
      SELECT MAX(b.pro_codigo)
      FROM
        vendaproduto  b
      WHERE
        a.pro_codigo = b.pro_codigo
        AND a.ven_codigo = b.ven_codigo 
        AND b.ven_codigo = 2432 
    )
  )

In all of them, removing the line corresponding to the code filter 2432, it is possible to remove all duplicates while keeping the latest record.