duplicate record elimination query
-
I have to write a query that eliminate duplicate records from the table..
please help me to do this in sql server 2000 databse
-
Hi R.B.Verma!
There are many different ways to do this. One of ways are to find all duplicates and move one of them to a temporary table, delete all duplicate records in the origin table, and last insert the rows from the temporary table into the origin table.
For example:
SELECT *
INTO #myTempTable
FROM <myTableWithDuplicates>
GROUP BY <col that indicate duplic>
HAVING COUNT(*) > 1
DELETE t1
FROM <myTableWithDuplicates> t1
INNER JOIN #myTempTable t2 ON t1.<col that indicate duplic> = t2.<col that indicate duplic>
INSERT INTO <myTableWithDuplicates>
SELECT * FROM #myTempTable
As I said, this is just one way to do it, and I guess you will have loads of more examples if you wait for others to reply.
Good luck.
robbac
___the truth is out there___ -
Hi Verma,
First of all your question is not clear. Give a Criteria on that you are finding the duplicate values. eg. Sample table Structure in which element is duplicating. as per the one result you got so far. there are lot of methods to do this but to select which one is suitable for your need to explain your problem.
/-Jeswanth
--------------------------------
-
first you need to select some columns who are compositely supposed to be unique, but are not.
then u need to write a query
select col1,col2,col3 from table
group by col1,col2,col3 have count(*)>1
i will suggest u make a view of it.
and then export that to any Excel file show it to the concerned person, and then delete it.
so that u will be absolutely sure, which 1 to delete and which 1 to not.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply