[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 772
  • Last Modified:

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

0
techzone12
Asked:
techzone12
  • 3
1 Solution
 
ralmadaCommented:
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
 
sameer2010Commented:
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
 
techzone12Author Commented:
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
 
ralmadaCommented:
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
 
ralmadaCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now