asked on

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?

sammySeltzer

But this will do the job though:

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

techzone12

ASKER

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

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cyberkiwi

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 ;

cyberkiwi

Further to the above, the ; is optional, but it must be a new statement block, hence the begin - end.

techzone12

ASKER

Thanks for the feedback. I will try the modified query according to your suggetion.

ralmada

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"