oracle delete行,oracle删除重复行delete repeat

oracle删除重复行delete repeat

2015-09-09

数据库产生大量的重复数据需要批量删除。

1、 创建表保存rowid

create table t1 (r varchar2(20));

2、insert into t1 select max(rowid) from chat_recorder_201509 where create_time between TO_DATE('20150907 19:00:00',

'YYYYMMDD HH24:MI:SS') and TO_DATE('20150908 01:00:00', 'YYYYMMDD HH24:MI:SS') group by RECORDER_ID having count(*) > 1;

28335 rows created.

3、删除rowid包含在t1表

delete from chat_recorder_201509 where rowid in (select r from t1);

28335 rows deleted.

4、检查结果select count(*) from chat_201509 where create_time between TO_DATE('20150907 19:00:00', 'YYYYMMDD HH24:MI:SS') and TO_DATE('20150908 01:00:00', 'YYYYMMDD HH24:MI:SS');

5、==========================================

select vst_phone from message_new where message_id in (select message_id from (select a.message_id,row_number() over(partition by message order by message) as n from message_new a where company_id=39415 and create_time > trunc(sysdate-2)) where n > 1);

6、delete from message_new where message_id in (select message_id from (select a.message_id,row_number() over(partition by message,vst_phone order by message) as n from message_new a where company_id=39415 ) where n > 1);

7、select user_name,count(1) row_number from customer group by user_name having count(user_name) > 1;

8、select cid,count(1) from CONFIG group by cid having count(1) > 150 order by count(1);

分类：数据库 | 标签： oracle |

相关日志