Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to delete duplicate data from SQL database?

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 08-06-2011
Member
 
Join Date: May 2011
Posts: 415
How to delete duplicate data from SQL database?
  

My problem is I have a Sql Database and it has lots of data I think about 1000 data entries now the problem is I have each record has an unique id but the other attributes are all same for some rows. Please tell me how to solve this problem which are the ways to solve and if you can suggest the exact queries to solve this problem? If anyone can suggest me anything about it please do it soon I want to release all the duplicate values as soon as possible?

Reply With Quote
  #2  
Old 08-06-2011
Member
 
Join Date: Mar 2010
Posts: 144
Re: How to delete duplicate data from SQL database?

Sql severs software?s are one of the best way to have a huge database for a well secured database serves. Now buy Sql database provides you with the facility of having very vast databases in the form of rows columns and tables so having a duplicate values is not a uncommon problem there are many people and many occasion when the duplication data problem arrives in Sql database so Sql has many ways to Solve this problem I would like to discuss on one of it And that is by using sub query and delete the common values , I can?t tell you the exact query because am not well known with your database but what I can say is use sub query to solve it . You can also take following query in action,
Code:
DELETE FROM ORIGINAL_TABLE T1 WHERE ROWID > (SELECT MIN (ROWID) FROM ORIGINAL_TABLE T2WHERE T1.KEY = T2.KEY);

DELETE FROM ORIGINAL_TABLE T1 WHERE ROWID > (SELECT MAX (ROWID) FROM ORIGINAL_TABLE T2WHERE T1.KEY = T2.KEY);
Reply With Quote
  #3  
Old 08-06-2011
Member
 
Join Date: Apr 2009
Posts: 567
Re: How to delete duplicate data from SQL database?

Having duplicate vales is not a new problem in Sql database but I would ask you that you provided a unique id to your record better than that why you dint make your table more secured such as it gives you an error when you are trying to enter and duplicate values this would solve your problem in the beginning itself giving an unique id to each an every record is not a good option anyways now you can solve it with a simple delete query first fetch the data which are have same value and then you can delete them by providing the condition in where clause hope it helps your problem.
Try this query out
Code:
CREATE TABLE NEWTABLE AS SELECT DISTINCT * FROM ORIGINALTABLE;
Then you can delete original table
Code:
DROP TABLE ORIGINAL_TABLE;
Then you have to rename the new table with the original table
Code:
RENAME NEWTABLE TO ORIGINALTABLE;
Reply With Quote
  #4  
Old 08-06-2011
Member
 
Join Date: May 2009
Posts: 620
Re: How to delete duplicate data from SQL database?

Hello friend I to have a database in Sqlserver but what I have done is while constructing a table I have provided each column with primary key constraint and some with unique where I knew duplicate values are not allowed so it has solved my problem in the begging now what you can do is delete those value by one by one which you are finding is duplicated and you don?t want them by simple queries, and once you are done with that add constraint to your columns for further problems. Hope you get solved your problem as soon as possible. Best of luck.
Reply With Quote
  #5  
Old 08-06-2011
Member
 
Join Date: May 2009
Posts: 503
Re: How to delete duplicate data from SQL database?

Below is the code which would help you to delete duplicate rows. Now the table must be having an identity column, which can be used to identify the duplicate records. Now in example table has ID as Identity Column and Columns which have duplicate data are duplicatecolumn3, duplicatecolumn2 and duplicatecolumn1
Code:
Delete FROM MY Table WHERE ID NOT IN (SELECT MAX (ID) FROM My Table) GROUP BY duplicatecolumn3, duplicatecoulmn2, duplicatecoumn1
This is an example now in your case there would be some changes.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to delete duplicate data from SQL database?"
Thread Thread Starter Forum Replies Last Post
How do I delete duplicate files? Lynette Media Player 8 17-12-2012 03:24 PM
How to remove the duplicate data in Oracle database Sharanya Software Development 4 27-02-2010 01:50 AM
How to prevent duplicate database entries Zombi Software Development 3 25-09-2009 02:59 PM
Is there any program to delete duplicate files. Atilla Windows Software 3 27-02-2009 03:21 PM
Delete duplicate emails and posts in Outlook with Duplicate Email Remover 2.15.2 Pyrotechnic Windows Software 0 08-01-2009 08:27 AM


All times are GMT +5.5. The time now is 02:58 AM.