SQL Server Performance Forum – Threads Archive

Delete Duplicate Rows in Sql sever 2000

Hi…
i want to remove the duplicates rows from a tabel…
according to the three columns data…
how can i do this [?] regards pankaj joshi

---

Do you have an identity column in the table?
What is the criteria which one of duplicate rows should stay? If there is an identity column and you want the first/last row inserted to stay then use group by clause and min/max aggregate.

---

check these:
http://www.sql-server-performance.com/dv_delete_duplicates.asp
http://www.sql-server-performance.com/rd_delete_duplicates.asp

---

Here is the code:
delete from t
from table t
join (select col1, col2, col3, min(t.id) as minID
from table
group by col1, col2, col3
having count(*) > 1
) as dups
on dups.col1 = t.col1 and dups.col2 = t.col2 and dups.col3 = t.col3 and dups.minID < t.id
[edited] Typo in the code.

---

Scratch this post. Oracle syntax. [<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />]<br /><br />John

---

I don’t remamber exactly, but I think that code was ok too.

---

Mirko, in sql server you can omit FROM after delete Other approach may be Delete from yourTable
where idcol not in (select min(id) from yourTable group by OtherCol) Madhivanan Failing to plan is Planning to fail

---

hi…
thx form u r reply
Mr. mmarovic
that is great query and running successfully
thx for u r support
regards pankaj joshi

---

You are wellcome. Thank you for the feedback.

---

]]>