How to remove duplicate data 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 2005 Forums

Transact-SQL (2005)

How to remove duplicate data from table

Author

Topic

satish.gorijala
Posting Yak Master

182 Posts

Posted - 2009-02-24 : 02:42:06

Hi, In my table i have lakhs of records. some of the records are repeating. I want to keep one record in table and remove the repeating ones. I dont know how many duplicates will be in table like this. The table contains lakhs of records
Ex:
ZID white black green
40 2 3 4
56 4 4 7
40 2 3 4
51 3 4 6
34 3 3 3
34 3 3 3

G. Satish

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-02-24 : 02:56:25

Hi,
See my blog post on how to delete the the duplicate rows -
http://mangalpardeshi.blogspot.com/2009/01/how-to-delete-duplicate-rows-in-sql.html

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-02-24 : 03:02:28

Here is example with your sample data

DECLARE @T TABLE(ZID int, white int, black int,green int)
INSERT INTO @T SELECT
40, 2, 3, 4 UNION ALL SELECT
56, 4, 4, 7 UNION ALL SELECT
40, 2, 3, 4 UNION ALL SELECT
51, 3, 4, 6 UNION ALL SELECT
34, 3, 3, 3 UNION ALL SELECT
34, 3, 3, 3

SELECT *
FROM @T

;WITH Cte AS
(
SELECT ZID, white, black, green, ROW_NUMBER() Over (PARTITION BY zId ORDER BY ZID) as Seq
FROM @T
)
DELETE FROM cte
WHERE Seq > 1

SELECT *
FROM @T

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-24 : 03:22:11

Mangal Pardeshi, i think that you wrong

i he have
40 2 2 2

so he did'nt want that will delete, and your solution delete duplicate on ZID only.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-24 : 03:23:48

select distinct * from urtable

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 03:24:54

then use ROW_NUMBER() Over (PARTITION BY zId,white,black,green ORDER BY ZID)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-24 : 03:25:16

delete t from
(select row_number() over ( partition by zid order by zid)as rn from urtable) t
where t.rn > 1

Mangal Pardeshi
Posting Yak Master

110 Posts

Posted - 2009-02-24 : 03:25:44

It depends, I given the solution on sample data provided. If want to check for complete row here you go
DECLARE @T TABLE(ZID int, white int, black int,green int)
INSERT INTO @T SELECT
40, 2, 3, 4 UNION ALL SELECT
56, 4, 4, 7 UNION ALL SELECT
40, 2, 3, 4 UNION ALL SELECT
51, 3, 4, 6 UNION ALL SELECT
34, 3, 3, 3 UNION ALL SELECT
34, 3, 3, 4 UNION ALL SELECT
34, 3, 3, 3

SELECT *
FROM @T

;WITH Cte AS
(
SELECT ZID, white, black, green, ROW_NUMBER() Over (PARTITION BY ZID, white, black, green ORDER BY ZID) as Seq
FROM @T
)
DELETE FROM cte
WHERE Seq > 1

SELECT *
FROM @T

Mangal Pardeshi
http://mangalpardeshi.blogspot.com

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-24 : 03:26:53

Try this also

declare @temp table (ZID int, white int,black int, green int)
insert into @temp
select 40, 2, 3, 4 union all
select 56,4 ,4, 7 union all
select 40 ,2 ,3, 4 union all
select 51, 3 ,4, 6 union all
select 34, 3, 3, 3 union all
select 34 ,3, 3, 3

delete t from
(select row_number() over ( partition by zid order by zid)as rn from @temp) t
where t.rn > 1

select * from @temp

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2009-02-24 : 09:43:17

This is how I do my dup run
select distinct *
into dbo.tblRawAccessOffShoreExTWFMC01

from dbo.tblRawAccessOffShoreExTWFMC

I then truncate the original table and update with the temp tbl then drop temp table

Whitmoj
If I have inspired one person today then my job is done.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources