Deleting Duplicate Records

[Najjar]

Hi, Please help
I'm trying to Delete Duplicate Records from my table I have this data

199 Name1 email1 add1
199 Name1 email1 add1
200 Name5 email5 add5
200 Name5 email5 add5
200 Name5 email5 add5
201 Name23 email23 add23

I want to delete the duplicate ones and keep only one of them that is what I want to keep

199 Name1 email1 add1
200 Name5 email5 add5
201 Name23 email23 add23

I'm using MS SQL

My table has more than 3500 Records
If you do not help I'll spend the rest of my life deleting the duplicate ones.

[penjaga_kubur]

hello can you give me the code!!!

[PinkPanther2003]

So how does the database know which record to keep?

The only way I can suggest is create a temp table using

--Create a deduplicated table
SELECT DISTINCT * INTO #NewTable
FROM OldTable

Go
--Delete the old table
DELETE * FROM OldTable

Go

--Repopulate the old table
INSERT INTO OldTable
SELECT * FROM #NewTable

Go
--Clean Up
DROP TABLE #NewTable

Go

However your referential integrity or other constraints may prevent this method from working so I guarentee nothing.

HTH,

Peter
Go

[Tiamiyu]

What you can do is to create a table that contain the list of the duplicate rows, delete the duplicates and the re-insert them back in. Eg:

1. create table temp as select col1,col2,col3,col4,...coln,count(*) no_of_dups from mytable
where group by col1,col2,col3,col4,...coln
having count(*) > 1;

2. Delete these duplicate rows from your table:

delete from mytable where
col1||col2||col3||...coln in (select col1||col2||col3||...coln from temp);

3. Insert the rows you deleted back to the original table
insert into mytab select
col1,col2,col3,col4,...coln from temp;

4. drop the temp table
Drop table temp;



I hope this works....


Tiamiyu Salau

[Najjar]

Thank you very much it worked from the first time but coz this is the first time i'm using this forum my reply was wrong and I thought that it went through
thank you for your time and sorry for the delay but i thought i replyed