How to delete the duplicate records in a table

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

How to delete the duplicate records in a table

Author

Topic

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-02 : 01:40:59

hi my table contains data like

1 a
1 a
1 a
1 a
1 a
2 b
2 b
3 c
3 c
3 c
3 c

i want to delete duplicate and my look like

1 a
2 b
3 c

Regards

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-02 : 02:12:18

with ashish as
(
select * ,row_number() over(partition by data order by id) as rn from ashish4
)
delete from ashish where rn>1
select * from ashish4

May Be like This....

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-02 : 02:26:45

Try this one & this isnt dependent on the version of Sqlserver that you have.

Delete from DupsTable
where UniqueColumn in
(
select a.UniqueColumn from DupsTable a, DupsTable b
where a.UniqueColumn != b.UniqueColumn
and a.DuplicateColumn = b.DuplicateColumn
and a.UniqueColumn < b.UniqueColumn
)

regards,
Anil Kumar.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-02 : 02:35:57

Hi Anil..

Unfortunately i did't have any unique column.

Regards

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-02 : 02:51:15

Sure then try this one & let me know if you run into any errors as this might work only on 2005 & beyond.

DELETE N
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS UniqueId
FROM Dtable
) AS N
WHERE UniqueId > 1

regards,
Anil Kumar.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-02 : 02:57:47

I'm using SQL SERVER 2000.. The row_number() function is not available here!!

What to do??

Regards

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-02 : 03:04:46

You can do it using a simple staging table. Just move all of the records into a Temp_Unique table and then drop the table itself.
Can you do that ?

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-04-02 : 03:13:03

This May Gonna work......

select * into aa from ashish4 group by id,data
truncate table ashish4
insert into ashish4 select * from aa
select * from ashish4
May Be Like This...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-02 : 03:16:45

Ya thanks Both of u..

Thats my last option..

But i try something in a query.its Challenging me..

Thanks for ur help..

Regards

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-04-02 : 03:19:38

Try this Once.

drop table #temp

declare @t table (A int ,b varchar(32))
insert into @t select 1 , 'a' union all select
1, 'a' union all select
1, 'a' union all select
1, 'a' union all select
1, 'a' union all select
1, 'a' union all select
2, 'b' union all select
2 ,'b' union all select
3 ,'c' union all select
3 ,'c' union all select
3 ,'c' union all select
3 ,'c'

select identity(int,1,1)as rid, * into #temp from @t

delete t1 from (
select t.rid,a,b,(select count(a) from #temp where rid <= t.rid and a= t.a)as row
from #temp t )t1
where t1.row > 1

select a,b from #temp

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-02 : 03:25:21

I just gave a thought & if you insist on going that way then this will do it.

DELETE D
FROM DupsTable D
JOIN
(
SELECT col1, col2 FROM DupsTable GROUP BY col1, Col2 HAVING COUNT(DISTINCT(*)>1)
)D1
ON D1.col1=D.col1
AND D1.col2=D.col2
WHERE D1.col1 IS NULL

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-02 : 03:46:40

Hi Anil,

If i match with the Distinct table as ur ref..

I will got duplicate entries in my main table na?

How it will work?

select d.id,d.name
FROM testtable D
JOIN
(
SELECT id, name FROM testtable GROUP BY id, name HAVING COUNT(DISTINCT(id))=1
)D1
ON D1.id=D.id
AND D1.name=D.name

wait i will try with left join..

Regards

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-04-02 : 03:50:42

Hey,

In left too the same result..

select d.id,d.name from
(SELECT id, name FROM testtable GROUP BY id, name HAVING COUNT(DISTINCT(id))=1) D
left outer join testtable d1 on D1.id=D.id and d1.name=d.name

Regards

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-02 : 03:52:19

Hey,

What I wrote in here & what you wrote are totally different... join & inner join will fetch you the same record set....anyways, Good luck Pal.

The whole idea in there is ...Keep the inner set of records & purge the outer one.

regards,
Anil Kumar.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources