Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to remove duplicate records from table T-SQL

Posted on 2010-09-01
5
Medium Priority
?
770 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 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