Solved

How to remove duplicate records from table T-SQL

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

707 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

17 Experts available now in Live!

Get 1:1 Help Now