CODEWITHSUNDEEP
mysqlsqlduplicatesdelete-row
Haradzieniec
Haradzieniec

Reputation: 9340

Remove duplicate rows from table with 1 column only?

I believe my question was asked on SO, but I didn't find the answer.

There is a mysql table mytable with one column mycolumn.

What is the mysql query to remove duplicates from a table?

Upvotes: 1

Views: 1203

Answers (2)

kstevens715
kstevens715

Reputation: 760

Here is one way to go about it as long as there are no triggers or foreign keys. Not tested because I'm on my phone, but should work. After this, maybe create a unique index on mycolumn to keep from getting duplicates.

Create table _mytable
Select distinct mycolumn from mytable;

delete from mytable;

Insert into mytable(mycolumn)
Select mycolumn from _mytable;

Drop table _mytable;

Upvotes: 1

Vargan
Vargan

Reputation: 1317

Only one column without pk or another column that you can use for see if they are different? if yes, this is a bad practice. Consider inserting a new column (number) and insert id for every record, than you can try this query:

delete from table 
where counter > 1 and inner_query.mycolumn = table.mycolumn and inner_query.col_id = table.col_id from 
(select mycolumn, col_id, count (mycolumn) counter  
 from table group by  mycolumn
) inner_query

than, you can add a primary key

Upvotes: 2

Related Questions