asked on

How to remove duplicate records from table T-SQL

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

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sameer2010

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

techzone12

ASKER

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

ralmada

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

ralmada

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