0

I have a table called ORG_MAP, it contained 200 records, I executed a query to copy those 200 records to other table called ORG_MAP_1 (duplicate table of ORG_MAP), but what happened was the same 200 records got inserted into the same ORG_MAP table.

Now I want to remove those duplicated records. I don't know how to do that.

Please help me. I'm using db visualizer8.0.11 version.

the query I used was:

INSERT INTO METRICS.ORG_MAP (REPORT_END_DATE,ENTITY,ENTITY_TYPE,RELATIONSHIP,TARGET,TARGET_TYPE)
SELECT REPORT_END_DATE,ENTITY,ENTITY_TYPE,RELATIONSHIP,TARGET,TARGET_TYPE
FROM METRICS.ORG_MAP
Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
madhu
  • 1,010
  • 5
  • 20
  • 38

1 Answers1

0

i dont think there is any direct solution for your problem but i have done some work around may be useful for you....

create table #table(id int,c varchar(2))
insert into #table values('1','a')
insert into #table values('1','a')
insert into #table values('2','b')
insert into #table values('2','b')
insert into #table values('3','c')
insert into #table values('3','c')
insert into #table values('4','d')
insert into #table values('5','d')

declare @table table(uid int,id int ,c varchar(2));


with tbl as (
    select ROW_NUMBER() OVER(ORDER BY id DESC) AS uid ,id,c from #table
) 

insert into @table select * from tbl

declare @tmpid int,@tmpuid int,@cnt int
declare tmpc cursor 
for select uid,id from @table;

open tmpc

fetch next from tmpc 
into @tmpuid,@tmpid

while @@FETCH_STATUS = 0
BEGIN
set @cnt = (select COUNT(id) from @table where id = @tmpid)
IF (@cnt > 1)
BEGIN   
    delete from @table where uid = @tmpuid      
END

fetch next from tmpc 
into @tmpuid,@tmpid

END 

CLOSE tmpc; 
DEALLOCATE tmpc;

select * from @table

drop table #table
Finisher001
  • 455
  • 1
  • 5
  • 18