Suppression des lignes en double de la table dans Oracle
Je teste quelque chose dans Oracle et remplis une table avec des exemples de données, mais dans le processus, j'ai accidentellement chargé des enregistrements en double, donc maintenant je ne peux pas créer une clé primaire en utilisant certaines des colonnes.
Comment puis-je supprimer toutes les lignes en double et ne laisser qu'une seule d'entre elles?
21 réponses
Utilisez la pseudocolumn rowid.
DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);
Où column1, column2, et column3 constituent la clé d'identification pour chaque enregistrement. Vous pouvez lister toutes vos colonnes.
delete from t
where rowid IN ( select rid
from (select rowid rid,
row_number() over (partition by
companyid, agentid, class , status, terminationdate
order by rowid) rn
from t)
where rn <> 1);
(Correction de la parenthèse manquante)
Solution 1)
delete from emp
where rowid not in
(select max(rowid) from emp group by empno);
Solution 2)
delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);
Solution 3)
delete from emp e1
where rowid not in
(select max(rowid) from emp e2
where e1.empno = e2.empno );
Pour sélectionner les doublons, seul le format de requête peut être:
SELECT GroupFunction(column1), GroupFunction(column2),...,
COUNT(column1), column1, column2...
FROM our_table
GROUP BY column1, column2, column3...
HAVING COUNT(column1) > 1
Donc, la requête correcte selon une autre suggestion est:
DELETE FROM tablename a
WHERE a.ROWID > ANY (SELECT b.ROWID
FROM tablename b
WHERE a.fieldname = b.fieldname
AND a.fieldname2 = b.fieldname2
AND ....so on.. to identify the duplicate rows....)
Cette requête conservera l'enregistrement le plus ancien dans la base de données pour les critères choisis dans le WHERE CLAUSE.
Oracle Certified Associate (2008)
Utiliser rowid -
delete from emp
where rowid not in
(select max(rowid) from emp group by empno);
Utilisation de self join -
delete from emp e1
where rowid not in
(select max(rowid) from emp e2
where e1.empno = e2.empno );
Solution 4)
delete from emp where rowid in
(
select rid from
(
select rowid rid,
dense_rank() over(partition by empno order by rowid
) rn
from emp
)
where rn > 1
);
1. solution
delete from emp
where rowid not in
(select max(rowid) from emp group by empno);
2. sloution
delete from emp where rowid in
(
select rid from
(
select rowid rid,
row_number() over(partition by empno order by empno) rn
from emp
)
where rn > 1
);
3.solution
delete from emp e1
where rowid not in
(select max(rowid) from emp e2
where e1.empno = e2.empno );
4. solution
delete from emp where rowid in
(
select rid from
(
select rowid rid,
dense_rank() over(partition by empno order by rowid
) rn
from emp
)
where rn > 1
);
5. solution
delete from emp where rowid in
(
select rid from
(
select rowid rid,rank() over (partition by emp_id order by rowid)rn from emp
)
where rn > 1
);
DELETE from table_name where rowid not in (select min(rowid) FROM table_name group by column_name);
Et vous pouvez également supprimer les enregistrements en double d'une autre manière
DELETE from table_name a where rowid > (select min(rowid) FROM table_name b where a.column=b.column);
Vous devriez faire un petit bloc pl / sql en utilisant un curseur pour la boucle et supprimer les lignes que vous ne voulez pas garder. Par exemple:
declare
prev_var my_table.var1%TYPE;
begin
for t in (select var1 from my_table order by var 1) LOOP
-- if previous var equal current var, delete the row, else keep on going.
end loop;
end;
DELETE FROM tableName WHERE ROWID NOT IN (SELECT MIN (ROWID) FROM table GROUP BY columnname);
delete from dept
where rowid in (
select rowid
from dept
minus
select max(rowid)
from dept
group by DEPTNO, DNAME, LOC
);
Le moyen le plus rapide pour de très grandes tables
-
Créer une table d'exception avec la structure ci-dessous: exceptions_table
ROW_ID ROWID OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) CONSTRAINT VARCHAR2(30) -
Essayez de créer une contrainte unique ou une clé primaire qui sera violée par les doublons. Vous obtiendrez un message d'erreur car vous avez des doublons. La table exceptions contiendra le rowids pour les lignes en double.
alter table add constraint unique --or primary key (dupfield1,dupfield2) exceptions into exceptions_table; -
Joignez votre table avec exceptions_table par rowid et supprimez dup
delete original_dups where rowid in (select ROW_ID from exceptions_table); -
Si le nombre de lignes à supprimer est important, créez une nouvelle table (avec toutes les subventions et tous les index) anti-jointure avec exceptions_table by rowid et renommez la table originale en table original_dups et renommez new_table_with_no_dups en table originale
create table new_table_with_no_dups AS ( select field1, field2 ........ from original_dups t1 where not exists ( select null from exceptions_table T2 where t1.rowid = t2.row_id ) )
create table abcd(id number(10),name varchar2(20))
insert into abcd values(1,'abc')
insert into abcd values(2,'pqr')
insert into abcd values(3,'xyz')
insert into abcd values(1,'abc')
insert into abcd values(2,'pqr')
insert into abcd values(3,'xyz')
select * from abcd
id Name
1 abc
2 pqr
3 xyz
1 abc
2 pqr
3 xyz
Delete Duplicate record but keep Distinct Record in table
DELETE
FROM abcd a
WHERE ROWID > (SELECT MIN(ROWID) FROM abcd b
WHERE b.id=a.id
);
run the above query 3 rows delete
select * from abcd
id Name
1 abc
2 pqr
3 xyz
Vérifiez les scripts ci-dessous -
1.
Create table test(id int,sal int);
2.
insert into test values(1,100);
insert into test values(1,100);
insert into test values(2,200);
insert into test values(2,200);
insert into test values(3,300);
insert into test values(3,300);
commit;
3.
select * from test;
, Vous verrez ici 6-dossiers.
4.exécuter la requête ci-dessous -
delete from
test
where rowid in
(select rowid from
(select
rowid,
row_number()
over
(partition by id order by sal) dup
from test)
where dup > 1)
-
select * from test;
Vous verrez que les enregistrements en double ont été supprimés.
Espérons que cela résout votre requête.
Merci :)
Je n'ai pas vu de réponses utilisant des expressions de table et des fonctions de fenêtre communes. C'est ce que je trouve le plus facile de travailler avec.
DELETE FROM
YourTable
WHERE
ROWID IN
(WITH Duplicates
AS (SELECT
ROWID RID,
ROW_NUMBER()
OVER(
PARTITION BY First_Name, Last_Name, Birth_Date)
AS RN
SUM(1)
OVER(
PARTITION BY First_Name, Last_Name, Birth_Date
ORDER BY ROWID ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING)
AS CNT
FROM
YourTable
WHERE
Load_Date IS NULL)
SELECT
RID
FROM
duplicates
WHERE
RN > 1);
Quelque chose à noter:
1) Nous vérifions uniquement la duplication sur les champs de la clause partition.
2) Si vous avez une raison de choisir un duplicata par rapport aux autres, vous pouvez utiliser une clause order by pour que cette ligne ait row_number () = 1
3) vous pouvez modifier le numéro dupliqué conservé en changeant la finale où clause à "Where RN > N" Avec N >= 1 (je pensais que N = 0 supprimerait toutes les lignes qui ont des doublons, mais il supprimerait simplement toutes les lignes).
4) a ajouté le champ de partition Sum la requête CTE qui marquera chaque ligne avec les lignes numériques du groupe. Donc, pour sélectionner des lignes avec des doublons, y compris le premier élément, utilisez "où cnt > 1".
create or replace procedure delete_duplicate_enq as
cursor c1 is
select *
from enquiry;
begin
for z in c1 loop
delete enquiry
where enquiry.enquiryno = z.enquiryno
and rowid > any
(select rowid
from enquiry
where enquiry.enquiryno = z.enquiryno);
end loop;
end delete_duplicate_enq;
Pour de meilleures performances, voici ce que j'ai écrit :
(voir plan d'exécution)
DELETE FROM your_table
WHERE rowid IN
(select t1.rowid from your_table t1
LEFT OUTER JOIN (
SELECT MIN(rowid) as rowid, column1,column2, column3
FROM your_table
GROUP BY column1, column2, column3
) co1 ON (t1.rowid = co1.rowid)
WHERE co1.rowid IS NULL
);