DELETE PERFECTLY DUPLICATE - 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 PERFECTLY DUPLICATE

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-20 : 01:30:13

I like to delete the perfectly duplicate rows from an table mean...
i just insert all the records 2 times in my table now i want to delete duplicates records...i am not working on SQL 2005 so that i am not able to use row_number() function as i m working on SQL 2000 so i just want to delete duplicate my table has 1,50,000 records and i just don't want to add a seed column in my table
so any body tell me about this

Thanks In Advance...

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-20 : 01:45:38

Insert all records in temp table. truncate original table. and then insert destinct records from temp table to original table.

Rahul Shinde

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-20 : 01:51:04

quote:
Originally posted by ra.shinde

Insert all records in temp table. truncate original table. and then insert destinct records from temp table to original table.

Rahul Shinde

Thanks for solution Sir,,,
But I dont need this one any other logical approach to this....

Thanks,,,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-20 : 01:55:00

Is this homework?
Every suggestion works very well for you.

E 12°55'05.63"
N 56°04'39.26"

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-20 : 01:59:33

quote:
Originally posted by Peso

Is this homework?
Every suggestion works very well for you.

E 12°55'05.63"
N 56°04'39.26"

No Its not a home work
just like to know any another logical approach....
jus want any simple solution well m also trying to find out any other solution,,,
so do help me?
any Logic.......

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-01-20 : 07:11:28

[code]DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ColA,ColB ORDER BY Col1, Col2) AS RecID FROM YourTable
) AS f
WHERE RecID > 1[/code]

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-20 : 07:17:31

quote:
Originally posted by DonAtWork

DELETE f
FROM (
SELECT ROW_NUMBER() OVER (PARTITION BY ColA,ColB ORDER BY Col1, Col2) AS RecID FROM YourTable
) AS f
WHERE RecID > 1

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

....function as i m working on SQL 2000 so i just want to ....

I think row_number() is not available in SQL SERVER 2000

Rahul Shinde

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-01-20 : 07:41:06

Yes Thats true,,,,,,,,
I M workin on SQL 2000 so its not needful for me...The Row_Number() function,,,,,

i aware of that functions,,,,
Well Thanks for reply,,,,,

i do it as Rahul Says Thanks For Replying,,,

But Still Finding for some more logical scripts to Delete ,,,
Thanks,,,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources