Delete duplicate rows

Recently I need to build an Axapta instance where the application has been modified while I only have a standard demo database.
Problem arises as the modified application has new indexes or change the table properties 'SaveDataPerCompany' to No instead of default Yes. Both may cause duplicate record problem. I don't want to just delete the entire record yet I need to synchronize the table with Axapta application.

It will be no problem if the table contains less than 20 records, I can just delete it manually. The problem is that I find a table has hundreds of records. Then I write this script to do this job less tedious.

--drop table #mytemptable -- This is needed only if run more than once
select distinct
module,type,groupid --specify as many as key fields
into #mytemptable
from pricediscgroup --this is the table name

--set variable s correspond to the key fields
declare @module int
declare @type int
declare @groupid varchar(20)
declare @numberofrows int
declare mycursor Cursor for
SELECT * from #mytemptable
open mycursor

fetch next from mycursor into @module,@type,@groupid

while @@fetch_status=0
begin

select @numberofrows = count(*) from pricediscgroup where
module = @module and type= @type and groupid = @groupid
select @numberofrows, @module,@type,@groupid
if @numberofrows > 1
begin
set @numberofrows = @numberofrows - 1 -- leave one row

set rowcount @numberofrows --the select within the subquery will return this number of rows,
delete from pricediscgroup where recid in (
select recid from pricediscgroup p2
where
p2.module = @module and p2.type= @type and p2.groupid = @groupid)

end

fetch next from mycursor into @module,@type,@groupid
end

close mycursor
deallocate mycursor
It does take time to change the table name and adding some variables depend on the duplicate fields found. But it takes less time rather than deleting many rows manually.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.