remove duplicate rows from 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

SQL Server 2000 Forums

SQL Server Development (2000)

remove duplicate rows from table

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2008-06-23 : 14:33:55

i have a log table that contains multiple entries or rows.

the rows contain 12 coloumns . My task is to remove duplicate rows in which all 12 coloumns are identical in the rows leaving only one row in the table.
how can i do it ?

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-06-23 : 14:36:22

It's easier to do in SQL Server 2005, but for 2000 you'll need to use something like this:
http://www.sqlteam.com/article/deleting-duplicate-records

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-23 : 23:53:55

[code]DELETE t
FROM Table t
INNER JOIN
(SELECT field1,field2,...,field12
FROM Table
GROUP BY field1,field2,...,field12
HAVING COUNT(*) >1)tmp
ON tmp.field1=t.field1
AND tmp.field2=t.field2
AND....
AND tmp.field12=t.field12
LEFT JOIN (select field1,field2,...,field12,MIN(PK) AS MinPK
FROM Table
GROUP BY field1,field2,...,field12)tmp2
ON tmp2.MinPK=t.PK
WHERE tmp2.MinPK IS NULL[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources