CODEWITHSUNDEEP
mysql
Awais Ahmad
Awais Ahmad

Reputation: 436

Removing Exact Duplicate records(same id as well) but keep one

I need a query which will delete all the records from the table which has exactly same row(even same id) but keep only one.

Here is my database structure:

      [ ID      STATUS   ] 
       '1', 'New Request'
       '1', 'New Request'
       '2', 'Old Request'

A query which will delete the first or maybe the second row but keep one record. So, there can be 3 , 4 or maybe more identical records but i have to delete all and keep only one. Please, assist me.

Upvotes: 1

Views: 2360

Answers (2)

Jacob Lambert
Jacob Lambert

Reputation: 7679

Depending on how big your table is, you could copy all distinct records into a temp table, truncate the original table, then copy back into the original:

CREATE TEMPORY TABLE tmp AS SELECT DISTINCT * FROM table;
TRUNCATE table;
INSERT INTO table SELECT * FROM tmp;

Upvotes: 1

Ajay Pathak
Ajay Pathak

Reputation: 17

Try following set of queries:

Select distinct [id], [status] into temptable from table

Go

Delete from table

Go

Insert into table select [id], [status] from temptable

Go

Drop table temptable

Go

Upvotes: 0

Related Questions