背景:坑爹程序员A写了个外系统接口,同步组织机构信息到本公司系统。但是接口并没有做UPDATE判断,每次跑都插入新数据,导致每个数据都有N条重复项。。组织机构表A
ID(long) CODE(varchar200) NAME(varchar200) ENABLE(n2 1可用,-1删除)要实现的效果是:将CODE和NAME重复的项,只保留ID最小的那一条可用,其他标记ENABLE字段为-1以上。求教。
ID(long) CODE(varchar200) NAME(varchar200) ENABLE(n2 1可用,-1删除)要实现的效果是:将CODE和NAME重复的项,只保留ID最小的那一条可用,其他标记ENABLE字段为-1以上。求教。
where ID in (select min(ID) from A group by CODE,NAME );
With tmp(ID,CODE,NAME,ENABLE) As
(select 1,'CODE1','NAME1',1 from dual union all
select 2,'CODE1','NAME1',1 from dual union all
select 3,'CODE1','NAME1',1 from dual union all
select 4,'CODE2','NAME2',1 from dual union all
select 5,'CODE2','NAME2',1 from dual union all
select 6,'CODE3','NAME3',1 from dual union all
select 7,'CODE4','NAME4',1 from dual union all
select 8,'CODE4','NAME4',1 from dual union all
select 9,'CODE4','NAME4',1 from dual union all
select 10,'CODE5','NAME5',1 from dual union all
select 11,'CODE5','NAME5',1 from dual union All
select 12,'CODE5','NAME5',1 from dual)
select Id,code,name,row_number() Over (Partition By CODE,Name Order By ID) Rn from tmp
set enable = '-1'
where rowid <> (select min(rowid) keep(dense_rank first order by id)
from tab_a
where code = a.code
and name = a.name);
update a set ENABLE=-1
--select *
from A a
inner join (
select min(id) id,code,name from a
group by code,name
having count(1)>1
) b on a.id<>b.id and a.code=b.code and a.name=b.name