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

Question

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.

https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql has some insights — xeo, Jan 30 '18 at 04:09
@xeo my cases has duplicate id's as well. And i also have to keep one and delete the rest. — Awais Ahmad, Jan 30 '18 at 04:11
it supports that - basically you forgot to add a unique key and that post describes how to correct it using several options. — xeo, Jan 30 '18 at 04:12
Just a side note, You can avoid such situations in the first place by making the ID column unique — Pubudu Dodangoda, Jan 30 '18 at 04:20
I reject the premise of the question. If you don't have a PRIMARY KEY, then you don't have a table. — Strawberry, Jan 30 '18 at 08:15

score 1 · Answer 1 · answered Jan 30 '18 at 04:18

1

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;

answered Jan 30 '18 at 04:18

Jacob Lambert

7,449
8
27
47

CREATE TEMPORARY TABLE tmp SELECT DISTINCT * FROM table; – Peter Drinnan Aug 30 '19 at 15:53

score 0 · Answer 2 · answered Jan 30 '18 at 04:15

0

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

answered Jan 30 '18 at 04:15

Ajay Pathak

17
2

MySQL doesn't use `Go`, that MSSQL. – Jacob Lambert Jan 30 '18 at 04:19

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

2 Answers2