Deleting Duplicate rows from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Deleting Duplicate rows from a table

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-09-24 : 05:43:30

Hello

I need to delete duplicate rows with the same values in three columns (CycleInstance, AccountNo, MobileNo). Fortunately, I happened upon a great article that does exactly that. However, I have copied the code but get the error:

[Server: Msg 170, Level 15, State 1, Line 45
Line 45: Incorrect syntax near ' set rowcount '.]

Can anyone please tell me what is wrong with the code (as I have no idea). Here is the code, the funny face locates where the error occurs:

declare
@iErrorVar int,
@cycleInstanceId bigint,
@accountNo varchar(30),
@mobileNo varchar (18),
@iReturnCode int,
@iCount int,
@chCount char(3),
@nvchCommand nvarchar (4000)

--Set initial environment
set rowcount 0
set nocount on

--Build cursor to find duplicated information
declare DelDupe cursor for
select count(*) as Amount, CycleInstanceId, AccountNo, MobileNo from J36
group by CycleInstanceId, AccountNo, MobileNo
having count(*)>1

open DelDupe
fetch next from DelDupe into @iCount,
@cycleInstanceId,
@accountNo,
@mobileNo
while (@@fetch_status=0)
begin

--calculate number of rows to delete for each grouping by subtracting 1
--from the total count for a given group
select @iCount = @iCount - 1
select @chCount = convert (char(3), @iCount)

--now build the rowcount and delete statements

select @nvchCommand = n' set rowcount ' + @chCount + 'delete IndTest '+
' where CycleInstanceId ' + char(34) + @cycleInstanceId + char(34) +
' and AccountNo = ' + char(34) + @accountNo + char(34) +
'and MobileNo = ' char(34) + @mobileNo + char(34)

--print the statement for your own viewing pleasure
print @nvchCommand

--execute the statement
exec sp_executesql @nvchCommand
select @iErrorVar = @@Error
if @iErrorVar <> 0
begin
return
end
fetch next from DelDupe into @iCount,
@cycleInstanceId,
@accountNo,
@mobileNo
end

Thanks in advance!!!!

Bex

Hearty head pats

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-24 : 05:51:20

Not sure that you need the set rowcoun in that line, but anyway you don't have a space before the 'delete ...' part so it is concatenating your @chCount with the delete to get an invalid command.

Raymond

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-09-24 : 05:51:41

I have just found this exmaple as well, and this looks a lot simpler:

SELECT DISTINCT * INTO #Temp FROM TABLENAME
go
TRUNCATE TABLENAME
go
INSERT INTO TABLENAME SELECT * FROM #Temp

What do people think? I really don't want to f*ck up as we have nearly a billion records in the table (only about 1/2,000 need deleting). So as you can imagine, i am a little nervous about what I do!

Hearty head pats

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-24 : 05:55:04

lolz, how about if you don't do deletions, instead try to migrate the distinct records to another table? if you have it all set then drop the old one (backup if you want) and rename the new table.

insert into table2
select distinct <fields here> from table1

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-09-24 : 06:01:06

Hmmmm, now that sounds like a plan...I like it...no deletions! It is the delete part that scares me, as you all know, once you have deleted, it has gone into the deleted abyss, never to return!

Thanks for your help! I shall report back to tell you my progress!

Hearty head pats

Kristen
Test

22859 Posts

Posted - 2004-09-24 : 09:19:46

The DROP TABLE and RENAME bit will only work [easily!] if there are no foreign keys on that table

Make a CREATE TABLE statement for "table2" using EM's GENERATE SQL on the existing table - that way you will get constraints, indexes, and all the rest of the gear.

As the table is large you might have to copy it in bits, otherwise it will take forever, and if it fails, or you want to abort it to go home!, it will take a second forever to ROLLBACK.

insert into table2
select distinct <fields here>
from table1
WHERE MyPK < 'A'

insert into table2
select distinct <fields here>
from table1
WHERE MyPK BETWEEN 'A' AND 'N'

etc.

(Does this include the PK of 'A' in one or other, but not both?)

Kristen

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-09-24 : 09:46:42

That makes sense. Thanks Kristen! Crikey, I thought this would take me.............hmmm.......bout an hour! Now I have 2 hours till home time and still struggling! Ho hum, the fun of working life!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources