Deleting Duplicate Rows in SQL 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

General SQL Server Forums

New to SQL Server Programming

Deleting Duplicate Rows in SQL Table

Author

Topic

Shaitan00
Starting Member

1 Post

Posted - 2007-11-16 : 13:13:54

I have an SQL tables [Keys] that has various rows such as:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External
3 Key1 Key1 InHouse
4 Key1 Key1 InHouse
5 Key1 Key1 InHouse

Obviously IDs 1,3,4,5 are all exactly the same and I would like to be left with only:

I cannot create a new table/database or change the unique identifier (which is currently ID) either. I simply need an SQL script I can run to clean out the duplicates (I know how they got there and the issue has been fixed but the Database is still currently invalid due to all these duplicate entires).

Any help would be greatly appreciated.
Thanks,

karuna
Aged Yak Warrior

582 Posts

Posted - 2007-11-16 : 13:26:37

I am sure, some body might have a better idea

DELETE FROM #Keys WHERE ID NOT IN
(
SELECT a.ID FROM #Keys a
JOIN (
Select min(ID) ID
From #Keys
Group by
Name,Path,Customer
) b
ON a.ID = b.ID
)

Thanks
Karunakaran

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-19 : 00:35:51

Hi!

Use setrowcount.suppose your table having 4 duplicate rows
then select all the four rows using select statement.then execute
set rowcount 3
delete the rows with condition
set rowcount 0
select * from tablename
now 3 duplicate rows only be deleted.

kiruthika
http://www.ictned.eu

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-19 : 03:33:01

quote:
Originally posted by kiruthika

Hi!

Use setrowcount.suppose your table having 4 duplicate rows
then select all the four rows using select statement.then execute
set rowcount 3
delete the rows with condition
set rowcount 0
select * from tablename
now 3 duplicate rows only be deleted.

kiruthika
http://www.ictned.eu

You need to use WHILE Loop to delete all duplicates if you use this approach

Madhivanan

Failing to plan is Planning to fail

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-19 : 04:25:43

Hi!
This is the table that i've used for processing

create table dup_example(id1 int, name varchar(20))
insert dup_example values(1,'a')
insert dup_example values(1,'a')
insert dup_example values(2,'b')
insert dup_example values(1,'a')

select * from dup_example

select id1,name from dup_example where id1=1
set rowcount 2
delete from dup_example where id1=1
set rowcount 0

here I'm not using while loop.

kiruthika
http://www.ictned.eu

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-19 : 04:26:51

Do read this:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-19 : 05:51:47

quote:
Originally posted by kiruthika

Hi!
This is the table that i've used for processing

create table dup_example(id1 int, name varchar(20))
insert dup_example values(1,'a')
insert dup_example values(1,'a')
insert dup_example values(2,'b')
insert dup_example values(1,'a')

select * from dup_example

select id1,name from dup_example where id1=1
set rowcount 2
delete from dup_example where id1=1
set rowcount 0

here I'm not using while loop.

kiruthika
http://www.ictned.eu

You are deleting for only one id
How would you generalise it to delete all duplicates?

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-19 : 11:48:11

What's so difficult? I assume you want to keep the first one


CREATE TABLE myKey99 ([ID] int, [Name] varchar(20), [Path] varchar(20), [Customer] varchar(20))
GO
INSERT myKey99(
	[ID], [Name], [Path], [Customer])
SELECT 1, 'Key1', 'Key1', 'InHouse' UNION ALL
SELECT 2, 'Key2', 'Key2', 'External' UNION ALL
SELECT 3, 'Key1', 'Key1', 'InHouse' UNION ALL
SELECT 4, 'Key1', 'Key1', 'InHouse' UNION ALL
SELECT 5, 'Key1', 'Key1', 'InHouse'
GO

SELECT * FROM myKey99
GO


DELETE 
  FROM myKey99
 WHERE ID NOT IN (
  	SELECT MIN([ID])
    	  FROM myKey99 i
      GROUP BY [Name], [Path], [Customer]
)
GO

SELECT * FROM myKey99
GO

DROP TABLE myKey99
GO

Now go put a unique index on that table

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-19 : 11:52:02

[code]
IF OBJECT_ID('zztest') IS not NULL
BEGIN
drop table zztest
END

GO

CREATE TABLE [dbo].[zztest] (
[T_ID] [int] IDENTITY (1, 1) NOT NULL ,
[ssn] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[stat1] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[zztest] WITH NOCHECK ADD
CONSTRAINT [PK_zztest] PRIMARY KEY CLUSTERED
(
[T_ID]
) ON [PRIMARY]
GO

CREATE INDEX [IX_zztest] ON [dbo].[zztest]([ssn]) ON [PRIMARY]
GO

insert into zztest(ssn,stat1)

select '123456789','abc'
union all select
'234567890','def'
union all select
'234567890','klw'
union all select
'233567390','zzl'
union all select
'234567890','qfg'
union all select
'123456789','pld'
union all select
'234567890','def'
union all select
'234567890','def'

-- show what we start with
SELECT * FROM zztest

--now dump dupes

DELETE
zztest
FROM
zztest a
inner join
(
select ssn,stat1
from
zztest
group by
ssn,stat1
having
count(*) > 1
) b
ON
a.ssn = b.ssn
and
a.stat1 = b.stat1

LEFT JOIN
(SELECT
PktoKeep = MIN(t_id)
FROM
zztest
GROUP BY
SSN,stat1
HAVING
COUNT(*) > 1
) c
ON
a.t_id = c.PktoKeep
WHERE
c.PktoKeep IS NULL

-- show what is left
select * from zztest

-- cleanup
drop table zztest
[/code]

I am sure Peter or someone else can tighten this code up.

EDIT: This is doing it without a NOT IN. Kind of long and wordy. Oh well.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

X002548
Not Just a Number

15586 Posts

Posted - 2007-11-19 : 13:18:26

Yes Don, that's a lot mopre simpler than mine

What's the plan look like?

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

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-19 : 14:22:42

why dont you cut and paste the code and find out

At least mine looks like I did a lot more work and am earning my keep!

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources