Usuwanie zduplikowanych wierszy z tabeli w Oracle


151

Testuję coś w Oracle i zapełniłem tabelę przykładowymi danymi, ale w trakcie tego procesu przypadkowo załadowałem zduplikowane rekordy, więc teraz nie mogę utworzyć klucza podstawowego za pomocą niektórych kolumn.

Jak mogę usunąć wszystkie zduplikowane wiersze i zostawić tylko jeden z nich?

Odpowiedzi:


306

Użyj rowidpseudokolumny.

DELETE FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3);

Gdzie column1, column2i column3uzupełnij klucz identyfikacyjny dla każdego rekordu. Możesz wymienić wszystkie swoje kolumny.


6
+1 Musiałem znaleźć dwa zduplikowane numery telefonów zakopane w ponad 12 000 rekordów. Zmieniono DELETE na SELECT i to znalazło je w kilka sekund. Zaoszczędziło mi mnóstwo czasu, dziękuję.
shimonyk

3
To podejście nie działa dla mnie. Nie wiem dlaczego. Kiedy zamieniłem „DELETE” na „SELECT *”, zwróciło wiersze, które chciałem usunąć, ale kiedy wykonałem polecenie „DELETE”, po prostu zawieszało się w nieskończoność.
aro_biz,

Mój też albo wisi, albo po prostu działa bardzo długo. Biegałem przez około 22 godziny i nadal idę. Stół ma 21 mln rekordów.
Cameron Castillo

Proponuję dodać dalsze filtrowanie do instrukcji WHERE, jeśli masz bardzo duży zestaw danych i jeśli to wykonalne, może to pomóc ludziom z długo działającymi zapytaniami.
Ricardo Sanchez

2
Jeśli zaznaczanie działa, ale usuwanie nie działa, może to być spowodowane rozmiarem wynikowego podzapytania. Może być interesujące, aby najpierw utworzyć tabelę z wynikiem podzapytania, zbudować indeks w kolumnie min (rowid), a następnie uruchomić instrukcję delete.
Wouter

14

Od Zapytaj Toma

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);

(poprawiono brakujący nawias)


1
Brak nawiasu w instrukcji. Zakładam, że powinno być na końcu?
Cameron Castillo

12

Z DevX.com :

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3...) ;

Gdzie kolumna1, kolumna2 itd. To klucz, którego chcesz użyć.


12
DELETE FROM tablename a
      WHERE a.ROWID > ANY (SELECT b.ROWID
                             FROM tablename b
                            WHERE a.fieldname = b.fieldname
                              AND a.fieldname2 = b.fieldname2)

1
Jeśli chodzi o mój komentarz powyżej na temat odpowiedzi, która została najwyżej głosowana, to ta prośba faktycznie rozwiązała mój problem.
aro_biz

2
Będzie to - dużo - wolniejsze na dużych stołach niż rozwiązanie Billa.
Wouter

8

Rozwiązanie 1)

delete from emp
where rowid not in
(select max(rowid) from emp group by empno);

Rozwiązanie 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
               );

Rozwiązanie 3)

delete from emp e1
         where rowid not in
          (select max(rowid) from emp e2
           where e1.empno = e2.empno ); 

6

utwórz tabelę t2 jako wybierz odrębną * od t1;


nie odpowiedź - distinct *weźmie każdy rekord różniący się co najmniej 1 symbolem w 1 kolumnie. Wszystko, czego potrzebujesz, to wybrać odrębne wartości tylko z kolumn, które chcesz utworzyć jako klucze podstawowe - odpowiedź Billa jest doskonałym przykładem tego podejścia.
Nogard

1
To było to, czego potrzebowałem (usuń całkowicie identyczne linie). Dzięki !
Emmanuel

Inną wadą tej metody jest to, że musisz utworzyć kopię swojej tabeli. W przypadku dużych tabel oznacza to zapewnienie dodatkowego obszaru tabel oraz usunięcie lub zmniejszenie obszaru tabel po skopiowaniu. Metoda Billa ma więcej zalet i nie ma żadnych dodatkowych wad.
Wouter

3

Powinieneś zrobić mały blok pl / sql używając kursora dla pętli i usunąć wiersze, których nie chcesz zachować. Na przykład:

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;

Uważam, że głos negatywny jest taki, że używasz PL / SQL, kiedy możesz to zrobić w SQL, na wypadek, gdybyś się zastanawiał.
WW.

7
To, że możesz to zrobić w SQL, nie oznacza, że ​​jest to jedyne rozwiązanie. Opublikowałem to rozwiązanie po obejrzeniu rozwiązania obsługującego tylko język SQL. Myślałem, że głosy były za błędnymi odpowiedziami.
Nick

3

Aby wybrać duplikaty, tylko format zapytania może być:

SELECT GroupFunction(column1), GroupFunction(column2),..., 
COUNT(column1), column1, column2...
FROM our_table
GROUP BY column1, column2, column3...
HAVING COUNT(column1) > 1

Tak więc prawidłowe zapytanie zgodnie z inną sugestią to:

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....)

To zapytanie zachowa najstarszy rekord w bazie danych dla kryteriów wybranych w WHERE CLAUSE.

Oracle Certified Associate (2008)


2

Najszybszy sposób na naprawdę duże stoły

  1. Utwórz tabelę wyjątków ze strukturą poniżej: exceptions_table

    ROW_ID ROWID
    OWNER VARCHAR2(30)
    TABLE_NAME VARCHAR2(30)
    CONSTRAINT VARCHAR2(30)
  2. Spróbuj utworzyć unikalne ograniczenie lub klucz podstawowy, który zostanie naruszony przez duplikaty. Otrzymasz komunikat o błędzie, ponieważ masz duplikaty. Tabela wyjątków będzie zawierała identyfikatory wierszy dla zduplikowanych wierszy.

    alter table add constraint
    unique --or primary key
    (dupfield1,dupfield2) exceptions into exceptions_table;
  3. Dołącz do swojej tabeli z zastosowaniem exceptions_table przez rowid i usuń dups

    delete original_dups where rowid in (select ROW_ID from exceptions_table);
  4. Jeśli liczba wierszy do usunięcia jest duża, utwórz nową tabelę (ze wszystkimi przydziałami i indeksami), zapobiegając łączeniu za pomocą wyjątków_tabeli według identyfikatora wiersza i zmień nazwę pierwotnej tabeli na oryginalną tabelę i zmień nazwę nowej_tabeli_with_no_dups na oryginalną tabelę

    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 )
    )

2

Korzystanie z rowid-

delete from emp
 where rowid not in
 (select max(rowid) from emp group by empno);

Korzystanie z samodzielnego łączenia

delete from emp e1
 where rowid not in
 (select max(rowid) from emp e2
 where e1.empno = e2.empno );

Cześć Tandale, Prosimy o używanie narzędzia do formatowania kodu podczas przesyłania odpowiedzi, ponieważ zwiększa to czytelność.
NSNoob

2

Rozwiązanie 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
);

Czy możesz trochę wyjaśnić?
Dieter Meemken

gęsta ranga z podziałem według daje rangę dla zduplikowanych wierszy o tej samej liczbie, na przykład trzy wiersze o randze 1, 1, 1 i rowid tworzą dla każdego wiersza jako unic i próbujemy usunąć te wiersze, które nie są zgodne.
DoOrDie

możemy użyć zarówno funkcji rank i dense_rank, ale myślę, że rank działa doskonale w tym scenariuszu.
DoOrDie

2

1. rozwiązanie

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. rozwiązanie

delete from emp e1
         where rowid not in
          (select max(rowid) from emp e2
           where e1.empno = e2.empno ); 

4. rozwiązanie

 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
);

2

5. rozwiązanie

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
    );

2
DELETE from table_name where rowid not in (select min(rowid) FROM table_name group by column_name);

możesz też usunąć zduplikowane rekordy w inny sposób

DELETE from table_name a where rowid > (select min(rowid) FROM table_name b where a.column=b.column);

2
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

1
DELETE FROM tableName  WHERE ROWID NOT IN (SELECT   MIN (ROWID) FROM table GROUP BY columnname);

Ta sama odpowiedź, co bardziej złożona odpowiedź Billa Jaszczura.
Wouter

1
delete from dept
where rowid in (
     select rowid
     from dept
     minus
     select max(rowid)
     from dept
     group by DEPTNO, DNAME, LOC
);

Czy możesz dodać więcej informacji o swojej drodze? Dzięki.
Reporter

1

Aby uzyskać najlepszą wydajność, oto co napisałem:
(zobacz plan wykonania)

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
);

1

Sprawdź poniższe skrypty -

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;    

Zobaczysz tutaj 6-rekordów.
4. uruchom poniższe zapytanie -

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)
  1. select * from test;

Zobaczysz, że zduplikowane rekordy zostały usunięte.
Mam nadzieję, że to rozwiąże Twoje pytanie. Dzięki :)


1

Nie widziałem żadnych odpowiedzi, które używają typowych wyrażeń tabelowych i funkcji okien. To jest to, z czym najłatwiej mi się pracuje.

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);

Coś do zapamiętania:

1) Sprawdzamy tylko pod kątem duplikacji pól w klauzuli partycji.

2) Jeśli masz jakiś powód, aby wybrać jeden duplikat zamiast innych, możesz użyć klauzuli order by, aby ten wiersz miał row_number () = 1

3) Możesz zmienić zachowany duplikat numeru, zmieniając ostatnią klauzulę where na „Where RN> N” z N> = 1 (myślałem, że N = 0 usunie wszystkie wiersze, które mają duplikaty, ale po prostu usunie wszystkie wiersze) .

4) Dodano pole Suma partycji, zapytanie CTE, które oznaczy każdy wiersz liczbą wierszy w grupie. Aby więc wybrać wiersze z duplikatami, w tym pierwszy element, użyj opcji „WHERE cnt> 1”.


0
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;

Główną wadą tej metody jest sprzężenie wewnętrzne. W przypadku dużych stołów będzie to dużo wolniejsze niż metoda Billa. Ponadto używanie PL / SQL do tego jest przesadą, możesz również użyć tego po prostu używając sql.
Wouter

0

rozwiązanie :

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
);
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.