Solved

How to remove duplicate records from table T-SQL

Posted on 2010-09-01
5
762 Views
Last Modified: 2012-06-27
SQL server 2008
I have a table that have a unique ID (datetime). I need to delete the duplicate records from the tabel.

This is what I have:
Time_Stamp        Colum1
9/1/2010 10:00   5643
9/1/2010 10:00   5632
9/1/2010 10:15   6710
9/1/2010 10:30   3210

I want to end up with this:
Time_Stamp        Colum1
9/1/2010 10:00   5643
9/1/2010 10:15   6710
9/1/2010 10:30   3210
or this:
Time_Stamp        Colum1
9/1/2010 10:00   5632
9/1/2010 10:15   6710
9/1/2010 10:30   3210

So basically eliminate one of the two (or more) duplicates, it does not matter which one (as long as one is left behind).

 Thanks

0
Comment
Question by:techzone12
  • 3
5 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33577885
try the below:
;with CTE as (

	select *, row_number() over (partition by Time_stamp order by Time_stamp) rn

	from yourtable

)

delete from CTe where rn > 1

Open in new window

0
 
LVL 13

Expert Comment

by:sameer2010
ID: 33578604
Try this
delete from a1 from your_table as a1 inner join your_table as b1

on a1.time_stamp = b1.time_stamp

where

a1.column1 < b1.column1

Open in new window

0
 

Author Comment

by:techzone12
ID: 33579000
sameer 2010,
Values in Column1 could be matching or different. They should not be user as criteria

ralmada,
your query works when tested on one table. When I try to apply it to multiple tables (using a cursor to scroll thru tables)  it's erroring out (syntax errors)





/* Query to identify tables with duplicate rows

 

DECLARE @name_var varchar(50)

DECLARE @get_name CURSOR

DECLARE @QueryString1 varchar(120)



-- Define cursor (which include all table names)

SET @get_name = CURSOR FOR

 SELECT name

 FROM dbo.sysobjects

 WHERE xtype = 'U' 



-- Open the cursor

OPEN @get_name

 

-- Fetch the cursor into the declared variable

FETCH NEXT FROM @get_name INTO @name_var

WHILE (@@FETCH_STATUS = 0) 

BEGIN

  if exists ( select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= @name_var and COLUMN_NAME = 'Date_Stamp' )

   Begin

       select @name_var

       

       set @QueryString1 = ';with CTE as (select *, ROW_NUMBER() over (PARTITION by Date_stamp order by date_stamp) rn from ' + @name_var + ')' 

       set @QueryString1 = @QueryString1 + ' delete from CTE where rn > 1'  

       

       --set @QueryString = 'select Date_Stamp from ' + @name_var + ' group by Date_Stamp having count(Date_Stamp) <>1'

       Execute (@QueryString1)

       

  End

     FETCH NEXT FROM @get_name INTO @name_var     

END



-- Cleanup

CLOSE @get_name

DEALLOCATE @get_name

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 33579172
try removing the ;
/* Query to identify tables with duplicate rows

 

DECLARE @name_var varchar(50)

DECLARE @get_name CURSOR

DECLARE @QueryString1 varchar(120)



-- Define cursor (which include all table names)

SET @get_name = CURSOR FOR

 SELECT name

 FROM dbo.sysobjects

 WHERE xtype = 'U' 



-- Open the cursor

OPEN @get_name

 

-- Fetch the cursor into the declared variable

FETCH NEXT FROM @get_name INTO @name_var

WHILE (@@FETCH_STATUS = 0) 

BEGIN

  if exists ( select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= @name_var and COLUMN_NAME = 'Date_Stamp' )

   Begin

       select @name_var

       

       set @QueryString1 = 'with CTE as (select *, ROW_NUMBER() over (PARTITION by Date_stamp order by date_stamp) rn from ' + @name_var + ')' 

       set @QueryString1 = @QueryString1 + ' delete from CTE where rn > 1'  

       

       --set @QueryString = 'select Date_Stamp from ' + @name_var + ' group by Date_Stamp having count(Date_Stamp) <>1'

       Execute (@QueryString1)

       

  End

     FETCH NEXT FROM @get_name INTO @name_var     

END



-- Cleanup

CLOSE @get_name

DEALLOCATE @get_name

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 33579244
plus your cursor definition is incorrect:
* Query to identify tables with duplicate rows

 

DECLARE @name_var varchar(50)

DECLARE @QueryString1 varchar(120)

DECLARE get_name CURSOR FOR 

 SELECT name

 FROM dbo.sysobjects

 WHERE xtype = 'U' 



OPEN get_name



FETCH NEXT FROM get_name

into @name_var





WHILE (@@FETCH_STATUS = 0) 

BEGIN

  if exists ( select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= @name_var and COLUMN_NAME = 'Date_Stamp' )

   Begin

       select @name_var

       

       set @QueryString1 = ';with CTE as (select *, ROW_NUMBER() over (PARTITION by Date_stamp order by date_stamp) rn from ' + @name_var + ')' 

       set @QueryString1 = @QueryString1 + ' delete from CTE where rn > 1'  

       

       --set @QueryString = 'select Date_Stamp from ' + @name_var + ' group by Date_Stamp having count(Date_Stamp) <>1'

       Execute (@QueryString1)

       

  End

     FETCH NEXT FROM get_name INTO @name_var     

END



.... so on ...

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now