SEO

#1 (**permalink**) 02-29-2008, 06:41 PM

I need help flagging duplicate records in ome tables I have.

For example if I have Table1 which conatins Field1, Field2 and Field3
like below

Field1 Field2 Field3 Field4
Paul 18 Null Null
Paul 18 Null Null
John 19 Null Null

How would I;
1. put a 'Y' in Field3 to mark the two records which are duplicates.
2. put a 'Y' in Field4 to mark ONLY ONE of the duplicate records.

Regards,

Ciarán

#2 (**permalink**) 02-29-2008, 06:41 PM

Pls check this article

http://www.sqlservercentral.com/colu...aterecords.asp

Rgds,
Miks

#3 (**permalink**) 02-29-2008, 06:41 PM

I have tried, My soultion may not be 100% perfect, Corrections welcome.

CREATE TABLE [dbo].[test2] (
[Name] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Age] [int] NOT NULL ,
[flag] [int] NULL ,
[flag1] [int] NULL
) ON [PRIMARY]
GO

Table Values

Alen 19 0 0
Alen 19 0 0
Aex 20 0 0

Code
-----

declare @name varchar(20), @age int, @counts int

declare cust cursor for
select count(1), Name, Age from test2 group by Name, Age having
count(Name) > 1

open cust fetch next from cust
into @counts, @name, @age

while @@fetch_status = 0

if(@counts) = 2
update test2 set flag = 1 where Name = @name and Age = @age

fetch next from cust into @counts, @name, @age

close cust
deallocate cust

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Identifying Duplicates in a table