delete duplicate record - 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

SQL Server 2000 Forums

Transact-SQL (2000)

delete duplicate record

Author

Topic

musawarman
Starting Member

1 Post

Posted - 2007-03-28 : 04:19:27

Hi friends,
can any body please tell me how can i delete duplicate records from a table .
If the table I have is with the following data

field1 field2 field3 field4 field5
----------------------------------------
a a a b c
a a a b c ------>deleted
a a a b d
a a a f d ------>deleted

finally, the table will look a like:
field1 field2 field3 field4 field5
----------------------------------------
a a a b c
a a a b d

Thanx& Regards
Musawarman

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 04:24:05

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60443&SearchTerms=delete,duplicate[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 04:29:33

[code]-- Prepare sample data
DECLARE @Sample TABLE (field1 VARCHAR, field2 VARCHAR, field3 VARCHAR, field4 VARCHAR, field5 VARCHAR)

INSERT @Sample
SELECT 'a', 'a', 'a', 'b', 'c' UNION ALL
SELECT 'a', 'a', 'a', 'b', 'c' UNION ALL
SELECT 'a', 'a', 'a', 'b', 'd' UNION ALL
SELECT 'a', 'a', 'a', 'b', 'd'

SET ROWCOUNT 1

DECLARE @Dummy INT
SELECT @Dummy = 1

WHILE @@ROWCOUNT > 0
DELETE s1
FROM @Sample AS s1
WHERE EXISTS (
SELECT *
FROM @Sample AS s2
WHERE s1.field1 = s2.field1
AND s1.field2 = s2.field2
AND s1.field3 = s2.field3
AND s1.field4 = s2.field4
AND s1.field5 = s2.field5
GROUP BY s2.field1,
s2.field2,
s2.field3,
s2.field4,
s2.field5
HAVING COUNT(*) > 1
)

SET ROWCOUNT 0

SELECT field1,
field2,
field3,
field4,
field5
FROM @Sample[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources