Удаление повторяющихся строк из таблицы в Oracle

Question

Удаление повторяющихся строк из таблицы в Oracle

101

Я тестирую что-то в Oracle и заполнял таблицу некоторыми примерами данных, но в процессе я случайно загружал дубликаты записей, поэтому теперь я не могу создать первичный ключ, используя некоторые из столбцов.

Как удалить все повторяющиеся строки и оставить только один из них?

juan 09 фев. 2009, в 17:04

Источник

Теги:

oracle

delete-row

duplicate-removal

21 ответ

11

Из Спросить Tom

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

(исправлена отсутствующая скобка)

Dead Programmer 18 март 2011, в 07:09

0

Скобки отсутствуют в утверждении. Я полагаю, это должно быть в конце?
Cameron Castillo 22 авг. 2013, в 06:02

10

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

user187624 09 нояб. 2009, в 07:06

1

Повторяю мой комментарий выше об ответе с наибольшим количеством голосов, именно этот запрос фактически решил мою проблему.
aro_biz 25 июнь 2012, в 12:06
2

На огромных столах это будет намного медленнее, чем решение Билла.
Wouter 15 май 2014, в 14:01

10

От DevX.com:

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

Если столбец1, столбец2 и т.д. - это ключ, который вы хотите использовать.

Mark 09 фев. 2009, в 18:05

5

создать таблицу t2 в качестве выделенного выделения * из t1;

Mohammed khaled 11 янв. 2013, в 18:03

0

не ответ - distinct * будет принимать каждую запись, которая отличается как минимум на 1 символ в 1 столбце. Все, что вам нужно, это выбрать отдельные значения только из столбцов, которые вы хотите сделать первичными ключами - ответ Билла является отличным примером такого подхода.
Nogard 11 янв. 2013, в 17:28
1

Это было то, что мне было нужно (убрать полностью идентичные строки). Спасибо !
Emmanuel 20 фев. 2013, в 11:43
0

Еще одним недостатком этого метода является то, что вам нужно создать копию таблицы. Для огромных таблиц это подразумевает предоставление дополнительного табличного пространства и удаление или сокращение табличного пространства после копирования. Метод Билла имеет больше преимуществ и никаких дополнительных недостатков.
Wouter 15 май 2014, в 13:59

Показать ещё 1 комментарий

4

Решение 1)

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

Решение 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
               );

Решение 3)

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

DoOrDie 31 дек. 2015, в 11:27

2

Чтобы выбрать дубликаты, только формат запроса может быть:

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

Итак, правильный запрос по другому предложению:

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

Этот запрос сохранит самую старую запись в базе данных для критериев, выбранных в WHERE CLAUSE.

Oracle Certified Associate (2008)

user1799846 17 июнь 2014, в 13:37

1

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

и вы также можете удалить дубликаты записей по-другому.

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

Md Wasi 07 янв. 2017, в 06:45

1

5. Решение

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

DoOrDie 10 фев. 2016, в 10:22

1

1. Решение

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. Решение

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

user5906974 10 фев. 2016, в 10:07

1

Решение 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
);

DoOrDie 31 дек. 2015, в 12:51

0

Можешь немного объяснить?
Dieter Meemken 31 дек. 2015, в 13:04
0

плотный ранг с разделением на дает ранг для дублирующихся строк с одинаковым номером, например три строки с рангом 1, 1, 1 и rowid создают для каждой строки как unic, и мы пытаемся удалить те строки, которые не совпадают.
DoOrDie 31 дек. 2015, в 13:18
0

мы можем использовать функции и ранга, и плотного_ранка, но я думаю, что ранг отлично работает в этом сценарии.
DoOrDie 31 дек. 2015, в 13:40

Показать ещё 1 комментарий

1

Использование rowid -

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

Использование self join -

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

Dnyaneshwar Tandale 28 дек. 2015, в 14:34

0

Привет Tandale, пожалуйста, используйте инструмент форматирования кода при отправке ответов, так как это повышает удобочитаемость.
NSNoob 28 дек. 2015, в 14:17

0

Я не видел ответов, которые используют общие выражения таблиц и функции окна. Это то, с чем мне легче всего работать.

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
              FROM
               YourTable
              WHERE
               Load_Date IS NULL)
     SELECT
      RID
     FROM
      duplicates
     WHERE
      RN > 1);

Darrel Lee 02 нояб. 2017, в 00:46

0

Проверьте скрипты ниже -

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;

Вы увидите здесь 6 записей.
4.run ниже запроса -

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;

Вы увидите, что дубликаты записей были удалены. Надеюсь, это решает ваш запрос. Спасибо:)

Rakesh Roshan 14 сен. 2017, в 09:50

0

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

Krunal Patel 03 июль 2017, в 11:18

0

Для лучшей производительности, вот что я написал:
(см. план выполнения)

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

Enguerrand JORE 11 март 2015, в 12:02

0

Самый быстрый способ для действительно больших таблиц

Создать таблицу исключений со структурой ниже: exceptions_table
```
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)
```
Попробуйте создать уникальное ограничение или первичный ключ, который будет нарушен дублирующими элементами. Вы получите сообщение об ошибке, потому что у вас есть дубликаты. Таблица исключений будет содержать rowids для повторяющихся строк.
```
alter table add constraint
unique --or primary key
(dupfield1,dupfield2) exceptions into exceptions_table;
```
Присоединитесь к своей таблице с помощью exceptions_table по rowid и удалите дубликаты
```
delete original_dups where rowid in (select ROW_ID from exceptions_table);
```
Если количество строк для удаления велико, то создайте новую таблицу (со всеми грантами и индексами), которая объединяется с exceptions_table по rowid и переименует исходную таблицу в таблицу original_dups и переименует new_table_with_no_dups в исходную таблицу
```
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 )
)
```

user2158672 30 май 2014, в 02:13

0

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

user3655760 20 май 2014, в 10:23

0

Можете ли вы добавить больше информации о вашем пути? Благодарю.
reporter 20 май 2014, в 09:16

0

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

JgSudhakar 12 янв. 2014, в 07:15

0

Тот же ответ, что и более сложный ответ Билла Ящерицы.
Wouter 15 май 2014, в 13:55

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;

Ashish sinha 26 нояб. 2013, в 10:34

0

Основным недостатком этого метода является внутреннее соединение. Для больших таблиц это будет намного медленнее, чем метод Билла. Кроме того, использование PL / SQL для этого является излишним, вы также можете использовать это, просто используя sql.
Wouter 15 май 2014, в 13:57

0

Вам нужно сделать небольшой блок pl/sql с помощью курсора для цикла и удалить строки, которые вы не хотите сохранять. Например:

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;

Nick 09 фев. 2009, в 18:14

0

Я полагаю, что отрицательное влияние заключается в том, что вы используете PL / SQL, когда вы можете сделать это в SQL, если вам интересно.
WW. 10 фев. 2009, в 01:39
6

То, что вы можете сделать это в SQL, не означает, что это единственное решение. Я опубликовал это решение после того, как увидел решение только для SQL. Я думал, что голоса были за неправильные ответы.
Nick 10 фев. 2009, в 02:43

Ещё вопросы

Скобки отсутствуют в утверждении. Я полагаю, это должно быть в конце?
Повторяю мой комментарий выше об ответе с наибольшим количеством голосов, именно этот запрос фактически решил мою проблему.
На огромных столах это будет намного медленнее, чем решение Билла.
не ответ - distinct * будет принимать каждую запись, которая отличается как минимум на 1 символ в 1 столбце. Все, что вам нужно, это выбрать отдельные значения только из столбцов, которые вы хотите сделать первичными ключами - ответ Билла является отличным примером такого подхода.
Это было то, что мне было нужно (убрать полностью идентичные строки). Спасибо !
Еще одним недостатком этого метода является то, что вам нужно создать копию таблицы. Для огромных таблиц это подразумевает предоставление дополнительного табличного пространства и удаление или сокращение табличного пространства после копирования. Метод Билла имеет больше преимуществ и никаких дополнительных недостатков.
плотный ранг с разделением на дает ранг для дублирующихся строк с одинаковым номером, например три строки с рангом 1, 1, 1 и rowid создают для каждой строки как unic, и мы пытаемся удалить те строки, которые не совпадают.
мы можем использовать функции и ранга, и плотного_ранка, но я думаю, что ранг отлично работает в этом сценарии.
Привет Tandale, пожалуйста, используйте инструмент форматирования кода при отправке ответов, так как это повышает удобочитаемость.
Можете ли вы добавить больше информации о вашем пути? Благодарю.
Тот же ответ, что и более сложный ответ Билла Ящерицы.
Основным недостатком этого метода является внутреннее соединение. Для больших таблиц это будет намного медленнее, чем метод Билла. Кроме того, использование PL / SQL для этого является излишним, вы также можете использовать это, просто используя sql.
Я полагаю, что отрицательное влияние заключается в том, что вы используете PL / SQL, когда вы можете сделать это в SQL, если вам интересно.
То, что вы можете сделать это в SQL, не означает, что это единственное решение. Я опубликовал это решение после того, как увидел решение только для SQL. Я думал, что голоса были за неправильные ответы.

Bill the Lizard · Accepted Answer · 2009-02-09T19-35-00.000Z

198

Лучший ответ

Использовать псевдокоманду rowid.

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

Где column1, column2 и column3 составляют идентификационный ключ для каждой записи. Вы можете указать все свои столбцы.

Bill the Lizard 09 фев. 2009, в 19:35

8

Отсюда: devx.com/tips/Tip/14665
Bill the Lizard 09 фев. 2009, в 17:44
4

+1 Мне нужно было найти два дубликата телефонных номеров в более чем 12 000 записей. Поменял DELETE на SELECT, и это нашло их за считанные секунды. Спасла мне кучу времени, спасибо.
shimonyk 23 сен. 2010, в 15:30
3

Этот подход не работал для меня. Я не знаю почему. Когда я заменил «DELETE» на «SELECT *», он вернул строки, которые я хотел удалить, но когда я выполнял с «DELETE», он просто зависал бесконечно.
aro_biz 25 июнь 2012, в 12:05
0

Моя тоже либо висит, либо просто выполняется очень долго. Бегал около 22 часов и все еще еду. Таблица имеет 21M записей.
Cameron Castillo 22 авг. 2013, в 05:57
0

Я предлагаю добавить дополнительную фильтрацию в оператор WHERE, если у вас очень большой набор данных и, если это возможно, это может помочь людям с длительными запросами.
Ricardo Sanchez 08 апр. 2014, в 16:58
2

Если выбор работает, а удаление - нет, это может быть связано с размером результирующего подзапроса. Может быть интересно сначала создать таблицу с результатом подзапроса, построить индекс по столбцу min (rowid), а затем выполнить оператор delete.
Wouter 15 май 2014, в 13:51
0

Отличное решение, спасибо.
UltraCommit 01 дек. 2015, в 11:42
0

Разве это не удалит только первый дубликат, если их несколько?
Darrel Lee 11 янв. 2018, в 18:42

Показать ещё 6 комментариев