Solved

How to remove duplicate records from table T-SQL

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL DATEADD 10 87
Trying to understand why my Index is so large 12 48
2008 to 2016 jump (SQL Server) 1 27
t-sql left join 2 25
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 …
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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