Delete dups - 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 2005 Forums

Transact-SQL (2005)

Delete dups

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-06-10 : 00:58:35

Hi,
I am trying to find and delete duplicate records:

CREATE TABLE A
(ID INT IDENTITY(1,1),
NAME VARCHAR(10)
);

INSERT INTO A (NAME) VALUES('A');
INSERT INTO A (NAME) VALUES('A');
INSERT INTO A (NAME) VALUES('A');
INSERT INTO A (NAME) VALUES('B');
INSERT INTO A (NAME) VALUES('B');
INSERT INTO A (NAME) VALUES('C');
INSERT INTO A (NAME) VALUES('C');
INSERT INTO A (NAME) VALUES('D');

Running this query should give me the duplicate records that will be deleted:
SELECT s1.ID,s1.NAME FROM A s1,A s2
WHERE s1.NAME=s2.NAME AND s1.ID<s2.ID

But this query returns:

ID NAME
1 A
1 A
2 A
4 B
6 C

Why is this giving a duplicate record for ID=1 NAME=A. This is happening for all rows where dup names have more than 2 records.

Thanks

--------------------
Rock n Roll with SQL

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-10 : 01:29:32

you query login is wrong!

put a one more insert statement after "D"

INSERT INTO A (NAME) VALUES('A');

Check what happen!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-10 : 01:32:47

Check this..

http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/

http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-06-10 : 02:58:06

Thanks Senthil but after adding another row I am getting this now:

ID NAME
1 A
1 A
2 A
4 B
6 C
1 A
2 A
3 A

There is something wrong in this query but not able to figure out the problem.

--------------------
Rock n Roll with SQL

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-10 : 03:11:28

quote:
Originally posted by rocknpop

Thanks Senthil but after adding another row I am getting this now:

ID NAME
1 A
1 A
2 A
4 B
6 C
1 A
2 A
3 A

There is something wrong in this query but not able to figure out the problem.

--------------------
Rock n Roll with SQL

Your query is totally wrong!

Re-write the query as per the links!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-06-10 : 05:20:35

Ok thanks, I figured out the problem in my query.

--------------------
Rock n Roll with SQL

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-06-10 : 05:28:11

Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources