Query to delete the duplicate rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query to delete the duplicate rows

Author

Topic

reacha
Starting Member

49 Posts

Posted - 2010-11-04 : 12:04:03

Can anyone help me with the query to delete duplicate rows from a table which does not have any identity column

Devart
Posting Yak Master

102 Posts

Posted - 2010-11-05 : 03:34:10

Hello,

For example:

create table t_table(
f1 int,
f2 int,
f3 int
);

insert into t_table values(1,1,1);
insert into t_table values(2,2,2);
insert into t_table values(2,2,2);
insert into t_table values(2,2,2);
insert into t_table values(3,3,3);
insert into t_table values(3,3,3);
insert into t_table values(4,4,4);

declare @table table
(f1 int,f2 int, f3 int);
insert into @table
select f1,f2,f3 from t_table group by f1,f2,f3 having count(*)>1;

delete from t_table
from t_table t1
inner join @table t2
on t1.f1=t2.f1 and t1.f2=t2.f2 and t1.f3=t2.f3

insert into t_table
select f1,f2,f3 from @table;

Best regards,

Devart,
SQL Server Tools:
dbForge Schema Compare
dbForge Data Compare
dbForge Query Builder

mma1979
Starting Member

1 Post

Posted - 2010-11-07 : 06:30:01

thanks too much it a very usful to me

Mohammed Mohammed Abdelhay

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-07 : 06:39:19

If you are using SQL Server 2005 or higher:
delete dt
from
(select
row_number() over (partition by f1,f2,f3 order by f1) as rownum,
*
from t_table) as dt
where rownum > 1

edit: typo

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources