Finding and Deleting Duplicate Records

I have a table that has many duplicate records and I need to delete all but
one of the duplicate records. Is there a way I can identify these records
with a sql statement?
Example record:
(Comment_id, site_id, date_entered, user_id, comment)
6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
non-franchise signage.
8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
non-franchise signage.Another thing to consider. The comment field is a text data type.
"Mike Collins" wrote:
> I have a table that has many duplicate records and I need to delete all but
> one of the duplicate records. Is there a way I can identify these records
> with a sql statement?
> Example record:
> (Comment_id, site_id, date_entered, user_id, comment)
> 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.
> 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.|||Mike,
Depending on the size of the comment field you could do something like this:
SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
FROM <TABLENAME>
HTH
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>I have a table that has many duplicate records and I need to delete all but
> one of the duplicate records. Is there a way I can identify these records
> with a sql statement?
> Example record:
> (Comment_id, site_id, date_entered, user_id, comment)
> 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.
> 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.|||Thanks, but how would I delete all the other records that I don't want?
"Jerry Spivey" wrote:
> Mike,
> Depending on the size of the comment field you could do something like this:
> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> FROM <TABLENAME>
> HTH
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >I have a table that has many duplicate records and I need to delete all but
> > one of the duplicate records. Is there a way I can identify these records
> > with a sql statement?
> >
> > Example record:
> > (Comment_id, site_id, date_entered, user_id, comment)
> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> > non-franchise signage.
> >
> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> > non-franchise signage.
>
>|||Mike,
Can you post the DDL for the table(s) so I don't have to guess about the
keys?
Thanks
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> Thanks, but how would I delete all the other records that I don't want?
> "Jerry Spivey" wrote:
>> Mike,
>> Depending on the size of the comment field you could do something like
>> this:
>> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
>> FROM <TABLENAME>
>> HTH
>> Jerry
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>> >I have a table that has many duplicate records and I need to delete all
>> >but
>> > one of the duplicate records. Is there a way I can identify these
>> > records
>> > with a sql statement?
>> >
>> > Example record:
>> > (Comment_id, site_id, date_entered, user_id, comment)
>> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
>> > non-franchise signage.
>> >
>> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
>> > non-franchise signage.
>>|||CREATE TABLE [dbo].[StatusRptComments] (
[Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
[Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
(
[Comment_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
"Jerry Spivey" wrote:
> Mike,
> Can you post the DDL for the table(s) so I don't have to guess about the
> keys?
> Thanks
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> > Thanks, but how would I delete all the other records that I don't want?
> >
> > "Jerry Spivey" wrote:
> >
> >> Mike,
> >>
> >> Depending on the size of the comment field you could do something like
> >> this:
> >>
> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> >> FROM <TABLENAME>
> >>
> >> HTH
> >>
> >> Jerry
> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >> >I have a table that has many duplicate records and I need to delete all
> >> >but
> >> > one of the duplicate records. Is there a way I can identify these
> >> > records
> >> > with a sql statement?
> >> >
> >> > Example record:
> >> > (Comment_id, site_id, date_entered, user_id, comment)
> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> >> > non-franchise signage.
> >> >
> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> >> > non-franchise signage.
> >>
> >>
> >>
>
>|||Mike,
What criteria do you want for the DELETE? I.e., ok the comment column is
the same for x records but the other columns may be different i.e,
comment_id, dateentered, etc... Which one record do you want to retain?
Thanks
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
> CREATE TABLE [dbo].[StatusRptComments] (
> [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
> [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
> CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
> (
> [Comment_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> "Jerry Spivey" wrote:
>> Mike,
>> Can you post the DDL for the table(s) so I don't have to guess about the
>> keys?
>> Thanks
>> Jerry
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
>> > Thanks, but how would I delete all the other records that I don't want?
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Mike,
>> >>
>> >> Depending on the size of the comment field you could do something like
>> >> this:
>> >>
>> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
>> >> FROM <TABLENAME>
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>> >> >I have a table that has many duplicate records and I need to delete
>> >> >all
>> >> >but
>> >> > one of the duplicate records. Is there a way I can identify these
>> >> > records
>> >> > with a sql statement?
>> >> >
>> >> > Example record:
>> >> > (Comment_id, site_id, date_entered, user_id, comment)
>> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
>> >> > non-franchise signage.
>> >> >
>> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
>> >> > non-franchise signage.
>> >>
>> >>
>> >>
>>|||Sorry...I want to keep the entry with the latest date. Thanks for your
patience with my not providing complete details up front.
"Jerry Spivey" wrote:
> Mike,
> What criteria do you want for the DELETE? I.e., ok the comment column is
> the same for x records but the other columns may be different i.e,
> comment_id, dateentered, etc... Which one record do you want to retain?
> Thanks
> Jerry
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
> > CREATE TABLE [dbo].[StatusRptComments] (
> > [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
> > [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
> > CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
> > (
> > [Comment_id]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > "Jerry Spivey" wrote:
> >
> >> Mike,
> >>
> >> Can you post the DDL for the table(s) so I don't have to guess about the
> >> keys?
> >>
> >> Thanks
> >>
> >> Jerry
> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> >> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> >> > Thanks, but how would I delete all the other records that I don't want?
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Mike,
> >> >>
> >> >> Depending on the size of the comment field you could do something like
> >> >> this:
> >> >>
> >> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> >> >> FROM <TABLENAME>
> >> >>
> >> >> HTH
> >> >>
> >> >> Jerry
> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >> >> >I have a table that has many duplicate records and I need to delete
> >> >> >all
> >> >> >but
> >> >> > one of the duplicate records. Is there a way I can identify these
> >> >> > records
> >> >> > with a sql statement?
> >> >> >
> >> >> > Example record:
> >> >> > (Comment_id, site_id, date_entered, user_id, comment)
> >> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> >> >> > non-franchise signage.
> >> >> >
> >> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> >> >> > non-franchise signage.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Mike,
Try:
BEGIN TRAN
DELETE #STATUSRPTCOMMENTS
FROM #STATUSRPTCOMMENTS S2
WHERE SITE_NO = S2.SITE_NO AND ADMINID = S2.ADMINID AND COMMENT = S2.COMMENT
AND S2.DATEENTERED <> (SELECT MAX(DATEENTERED) FROM #STATUSRPTCOMMENTS S1
WHERE S1.SITE_NO = S2.SITE_NO)
--ROLLBACK TRAN
--OR
--COMMIT TRAN
My mind is shot today so use this explicit tran and see if it works if not
roll back and tell me what is wrong and I'll look at it this weekend.
HTH
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:6476D0E1-1054-429E-A456-35982AA76A29@.microsoft.com...
> Sorry...I want to keep the entry with the latest date. Thanks for your
> patience with my not providing complete details up front.
> "Jerry Spivey" wrote:
>> Mike,
>> What criteria do you want for the DELETE? I.e., ok the comment column is
>> the same for x records but the other columns may be different i.e,
>> comment_id, dateentered, etc... Which one record do you want to retain?
>> Thanks
>> Jerry
>>
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
>> > CREATE TABLE [dbo].[StatusRptComments] (
>> > [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
>> > [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ,
>> > [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
>> > NULL ,
>> > [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
>> > CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
>> > (
>> > [Comment_id]
>> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
>> > GO
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Mike,
>> >>
>> >> Can you post the DDL for the table(s) so I don't have to guess about
>> >> the
>> >> keys?
>> >>
>> >> Thanks
>> >>
>> >> Jerry
>> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
>> >> > Thanks, but how would I delete all the other records that I don't
>> >> > want?
>> >> >
>> >> > "Jerry Spivey" wrote:
>> >> >
>> >> >> Mike,
>> >> >>
>> >> >> Depending on the size of the comment field you could do something
>> >> >> like
>> >> >> this:
>> >> >>
>> >> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
>> >> >> FROM <TABLENAME>
>> >> >>
>> >> >> HTH
>> >> >>
>> >> >> Jerry
>> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>> >> >> >I have a table that has many duplicate records and I need to
>> >> >> >delete
>> >> >> >all
>> >> >> >but
>> >> >> > one of the duplicate records. Is there a way I can identify these
>> >> >> > records
>> >> >> > with a sql statement?
>> >> >> >
>> >> >> > Example record:
>> >> >> > (Comment_id, site_id, date_entered, user_id, comment)
>> >> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
>> >> >> > non-franchise signage.
>> >> >> >
>> >> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
>> >> >> > non-franchise signage.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||That works great...thanks Jerry.
"Jerry Spivey" wrote:
> Mike,
> Try:
> BEGIN TRAN
> DELETE #STATUSRPTCOMMENTS
> FROM #STATUSRPTCOMMENTS S2
> WHERE SITE_NO = S2.SITE_NO AND ADMINID = S2.ADMINID AND COMMENT = S2.COMMENT
> AND S2.DATEENTERED <> (SELECT MAX(DATEENTERED) FROM #STATUSRPTCOMMENTS S1
> WHERE S1.SITE_NO = S2.SITE_NO)
> --ROLLBACK TRAN
> --OR
> --COMMIT TRAN
> My mind is shot today so use this explicit tran and see if it works if not
> roll back and tell me what is wrong and I'll look at it this weekend.
> HTH
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:6476D0E1-1054-429E-A456-35982AA76A29@.microsoft.com...
> > Sorry...I want to keep the entry with the latest date. Thanks for your
> > patience with my not providing complete details up front.
> >
> > "Jerry Spivey" wrote:
> >
> >> Mike,
> >>
> >> What criteria do you want for the DELETE? I.e., ok the comment column is
> >> the same for x records but the other columns may be different i.e,
> >> comment_id, dateentered, etc... Which one record do you want to retain?
> >>
> >> Thanks
> >>
> >> Jerry
> >>
> >>
> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> >> news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
> >> > CREATE TABLE [dbo].[StatusRptComments] (
> >> > [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
> >> > [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >> > [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ,
> >> > [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >> > [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >> > [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
> >> > NULL ,
> >> > [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
> >> > CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
> >> > (
> >> > [Comment_id]
> >> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> >> > GO
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Mike,
> >> >>
> >> >> Can you post the DDL for the table(s) so I don't have to guess about
> >> >> the
> >> >> keys?
> >> >>
> >> >> Thanks
> >> >>
> >> >> Jerry
> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> >> >> > Thanks, but how would I delete all the other records that I don't
> >> >> > want?
> >> >> >
> >> >> > "Jerry Spivey" wrote:
> >> >> >
> >> >> >> Mike,
> >> >> >>
> >> >> >> Depending on the size of the comment field you could do something
> >> >> >> like
> >> >> >> this:
> >> >> >>
> >> >> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> >> >> >> FROM <TABLENAME>
> >> >> >>
> >> >> >> HTH
> >> >> >>
> >> >> >> Jerry
> >> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >> >> >> >I have a table that has many duplicate records and I need to
> >> >> >> >delete
> >> >> >> >all
> >> >> >> >but
> >> >> >> > one of the duplicate records. Is there a way I can identify these
> >> >> >> > records
> >> >> >> > with a sql statement?
> >> >> >> >
> >> >> >> > Example record:
> >> >> >> > (Comment_id, site_id, date_entered, user_id, comment)
> >> >> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> >> >> >> > non-franchise signage.
> >> >> >> >
> >> >> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> >> >> >> > non-franchise signage.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>