Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Delete duplicate rows from table SQL 2008

Posted on 2010-09-01
7
Medium Priority
?
762 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
[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
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 29

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 2000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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 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.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

596 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