How to delete duplicate data from SQL database?

[Dabert]

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?

[Mattabesic]

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,

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);

[Dwarner]

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

CREATE TABLE NEWTABLE AS SELECT DISTINCT * FROM ORIGINALTABLE;

Then you can delete original table

DROP TABLE ORIGINAL_TABLE;

Then you have to rename the new table with the original table

RENAME NEWTABLE TO ORIGINALTABLE;

[Steyn]

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.

[Vandam]

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

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.