I have now performed a two-time process where it should be processed only once. So my table was populated twice with the same data.
tb_kardex
cd_kardex | cd_item
tb_kardex
| 1 | 45875 |
| 2 | 45876 |
| 3 | 45877 |
| 4 | 45875 |
| 5 | 45876 |
| 6 | 45877 |
How to delete only the "second" record of the same item?
Do this:
DELETE a FROM nomes AS a, nomes AS b WHERE a.nome=b.nome AND a.id < b.id
Code find it here.
UPDATING
For postgreSQL 9.3 it would look like this: ( Here a Fiddle running )
DELETE FROM tb_kardex a
where exists (select 1 from tb_kardex b
WHERE a.cd_kardex=b.cd_kardex
AND a.cd_item < b.cd_item);
DELETE FROM tb_kardex
WHERE cd_kardex IN (
SELECT cd_kardex
FROM
(
SELECT cd_kardex,
ROW_NUMBER() OVER
(PARTITION BY cd_item ORDER BY cd_kardex asc) AS rn -- CRIA UM CONTAGEM DE PARA CADA CD_ITEM REPETIDO
FROM tb_kardex -- É IMPORTANTE A UTILIZAÇÃO DO ORDER PARA MANTER O RN 2
) A -- NO ULTIMO cd_kardex
WHERE rn = 2
)
If you have made a process repeated several times having more than 2 rows change the rn > 1 criterion.
As this answer in SOen you can create a temporary table and use DISTINCT :
CREATE TABLE tmp ...
INSERT INTO tmp SELECT DISTINCT * FROM t;
DROP TABLE t;
ALTER TABLE tmp RENAME TO t;
I recommend that you back up before