[Webinar] Streamline your web hosting managementRegister Today

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

Delete duplicate rows from table SQL 2008

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
techzone12
Asked:
techzone12
  • 2
  • 2
  • 2
  • +1
1 Solution
 
sammySeltzerCommented:
But this will do the job though:

DELETE FROM mytable WHERE yourid IN
    (SELECT yourid
    FROM mytable
    GROUP BY yourid
    HAVING COUNT(*) > 1)
0
 
techzone12Author Commented:
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
 
ralmadaCommented:
>>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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
Further to the above, the ; is optional, but it must be a new statement block, hence the begin - end.
0
 
techzone12Author Commented:
Thanks for the feedback.  I will try the modified query according to your suggetion.
0
 
ralmadaCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now