为避免数据丢失,建议LZ:1.新建一张表,字段和原表一样(create new_table ...): 2.建立如id的关键字; 3.insert into new_table as select * from old_table;因为加了主关键字,所以记录不会重复。
导入两次的话,不带条件检索出来结果应该是第一个导入的在前面,第二次导入的在后面的。 delete from table where rowno <= 一次导入件数 这样是删除掉第一次导入的。 之前最好先做个dump。
为避免数据丢失,建议LZ:1.新建一张表,字段和原表一样(create new_table ...): 2.建立如id的关键字; 3.insert into new_table as select * from old_table;因为加了主关键字,所以记录不会重复。
delete t where rowid not in (select max(rowid) from t group by name )
增加一个ROW_NUMBER()OVER(ORDER BY )(确定唯一一条的组合主键或者单一主键做为排序条件。) 然后将所以单数或者双数删除即可!
高效的方法: DELETE FROM A A1 WHERE ROWID IN (SELECT RD FROM (SELECT ROWID RD, ROW_NUMBER() OVER(PARTITION BY nameCol ORDER BY ROWID) RN FROM A) WHERE RN <> 1);
delete from tb where rowid not in (select max(rowid) from tb group by name)
分组获得做大row_id,然后删除小于的就行了
delete from tb where rowid not in (select max(rowid) from tb group by name)
create table lsb_xx select distinct * from xx; truncate table xx; insert into xx select * from lsb_xx; drop table lsb_xx; 注意备份及 commit
这个问题刚才在哪个帖子发过了? 我再发一次--以下是删除重复数据的3种方案 --3种方案都是最优化的 --object_name, object_id. 这2个字段用来判断重复--1. delete from x where rowid in ( select rd from ( select rowid rd ,row_number() over(partition by object_name, object_id order by rowid) rn from x ) x where rn > 1 )--2. delete from x where rowid not in ( select max(rowid) from x group by object_name, object_id )
--3. create table tmp_x AS select x1.(字段列表..略) from ( select x.*, row_number() over(partition by object_name, object_id order by rowid) rn from x ) x1 where rn = 1;
truncate table x;insert into x select * from tmp_x; drop table tmp_x;
楼上这种总结的方法不错啊,不过第二种方法,对于 oracle 来说,简洁明了
我个人意见,insert into new_table as select distinct * from old_table;
我个人意见,insert into new_table as select distinct * from old_table;
我个人意见,insert into new_table as select distinct * from old_table;
2.建立如id的关键字;
3.insert into new_table as select * from old_table;因为加了主关键字,所以记录不会重复。
delete from table where rowno <= 一次导入件数
这样是删除掉第一次导入的。
之前最好先做个dump。
2.建立如id的关键字;
3.insert into new_table as select * from old_table;因为加了主关键字,所以记录不会重复。
然后将所以单数或者双数删除即可!
DELETE FROM A A1
WHERE ROWID IN (SELECT RD
FROM (SELECT ROWID RD,
ROW_NUMBER() OVER(PARTITION BY nameCol ORDER BY ROWID) RN
FROM A)
WHERE RN <> 1);
create table lsb_xx select distinct * from xx;
truncate table xx;
insert into xx select * from lsb_xx;
drop table lsb_xx;
注意备份及 commit
我再发一次--以下是删除重复数据的3种方案
--3种方案都是最优化的
--object_name, object_id. 这2个字段用来判断重复--1.
delete
from x
where rowid in
(
select rd
from (
select rowid rd
,row_number() over(partition by object_name, object_id order by rowid) rn
from x
) x
where rn > 1
)--2.
delete
from x
where rowid not in (
select max(rowid)
from x
group by object_name, object_id
)
--3.
create table tmp_x
AS
select x1.(字段列表..略)
from
(
select x.*, row_number() over(partition by object_name, object_id order by rowid) rn
from x
) x1
where rn = 1;
truncate table x;insert into x
select *
from tmp_x; drop table tmp_x;