-
Deleting Duplicate Records
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.
-
can you give me the code!!!
hello can you give me the code!!!
-
DELETING DUPLICATES
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
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|