Solved

Delete duplicate rows from table SQL 2008

Posted on 2010-09-01
7
755 Views
Last Modified: 2012-06-22
SQL 2008

I have a query that deletes duplicate row form SQL table. The query looks like this:

with CTE as (
      select *, row_number() over (partition by Time_stamp order by Time_stamp) rn
      from yourtable
)
delete from CTe where rn > 1;

Query works fine. I just need to expand it's function however. I need to be able to scroll thru all tables in the database and find duplicate rows (leaving just one behind). So I am thinking  to use a cursor.

Each table has a id colum called "Date_Stamp". Duplicate Rows are those that have matching id (keys), regardless of the reset of data in other columns.

How do I achieve that?
0
Comment
Question by:techzone12
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 33579473
But this will do the job though:

DELETE FROM mytable WHERE yourid IN
    (SELECT yourid
    FROM mytable
    GROUP BY yourid
    HAVING COUNT(*) > 1)
0
 

Author Comment

by:techzone12
ID: 33579577
Yes. It looks actually simpler.

Now I need to scroll thru all tables in the database and delete duplicate rows. How do I do this?

Thanks
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33579696
>>DELETE FROM mytable WHERE yourid IN
    (SELECT yourid
    FROM mytable
    GROUP BY yourid
    HAVING COUNT(*) > 1) <<
Wrong, that will delete all rows where there are duplicates, including the one you want to leave behind!!
You can try the below if you want to avoid a cursor
 

exec sp_MSforeachtable '
if exists (select 1 from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= ''?'' and COLUMN_NAME = ''Date_Stamp'')
	with CTE as (
	      select *, row_number() over (partition by Time_stamp order by Time_stamp) rn
	      from [?]
	)
	delete from CTe where rn > 1'

Open in new window

0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33584205
Just a correction to the code by ralmada

exec sp_MSforeachtable '
if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME= ''?'' and COLUMN_NAME = ''Date_Stamp'')
begin
;with CTE as (
            select *, row_number() over (partition by Date_Stamp order by Date_Stamp) rn
            from [?]
      )
      delete from CTe where rn > 1
end'

Partition should be by date_stamp or whatever name your real column is, and with CTE requires a leading ;
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33584209
Further to the above, the ; is optional, but it must be a new statement block, hence the begin - end.
0
 

Author Comment

by:techzone12
ID: 33585287
Thanks for the feedback.  I will try the modified query according to your suggetion.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33587343
Yeah, the column name should be date_stamp. I just copied and pasted your query without checking the column name. So where it reads "Time_stamp" it should read "Date_Stamp"
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

809 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