Solved

How to remove duplicate records from table T-SQL

Posted on 2010-09-01
5
763 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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