| Author |
 Topic  |
|
|
reacha
Starting Member
49 Posts |
 Posted - 11/04/2010 : 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 - 11/05/2010 : 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
Egypt
1 Posts |
Posted - 11/07/2010 : 06:30:01
|
thanks too much it a very usful to me
Mohammed Mohammed Abdelhay |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 11/07/2010 : 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. |
Edited by - webfred on 11/07/2010 06:39:51 |
|
|
| |
Topic |
|
|
|
Query to delete the duplicate rows
