delete duplicates - SQL Server Forums

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

delete duplicates

Author

Topic

craigmacca
Posting Yak Master

142 Posts

Posted - 2008-03-11 : 16:13:32

Hi i need a query to check a table and if any duplicates of the column called "MessageID" and if there are any duplicates then delete them leaving just the one unique MessageID

so i have

MessageID, Number, Text.

12,33333333333,hello
12,33333333333,hello - Delete this one
12,33333333333,hello - Delete this one
14,55555555555,new

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-11 : 16:23:01

Try the Search functionality. Enter "Delete Duplicates" and you will get many threads with wonderful responses.

X002548
Not Just a Number

15586 Posts

Posted - 2008-03-11 : 17:05:15

SELECT DISTINCT MessageID, Number, Text INTO NewTable FROM OldTable

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 02:27:50

You still haven't specified wether or not you are using SQL Server 2000 or SQL Server 2005.

DELETE f
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY MessageID, Number, Text ORDER BY MessageID) AS RecID
) AS f
WHERE RecID > 1

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources