利用临时表去除数据库重复数据

第一步:备份数据并创建临时表
create table jc_content_ext_bak as select * from jc_content_ext;

第二步:临时表创建索引
alter table jc_content_ext_bak add primary key (content_id);
alter table jc_content_ext_bak add index idx_jc_content_ext_01 (title);
alter table jc_content_ext_bak add UNIQUE idx_jc_content_ext_02 (content_id,title);

第三部:删除
delete from jc_content_ext where content_id in(
select content_id from jc_content_ext_bak
where content_id not in
(select max(content_id) content_id from jc_content_ext_bak
group by title HAVING count(content_id)>1)
and title in
(select title from jc_content_ext_bak group by title HAVING count(content_id)>1));

 

create table t_id as
select content_id from jc_content_ext_bak
where content_id not in
(select max(content_id) content_id from jc_content_ext_bak
group by title HAVING count(content_id)>1)
and title in
(select title from jc_content_ext_bak group by title HAVING count(content_id)>1);

delete from jc_content_ext where content_id in(select * from t_id );


第四步:测试并验证数据
select title from jc_content_ext group by title HAVING count(content_id)>1);

第五步:删除临时表
drop table jc_content_ext_bak ;
drop table t_id;

posted @ 2017-05-19 16:53  祥福有梦想  阅读(318)  评论(0编辑  收藏  举报