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?
Add a comment
|
2 Answers
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
2 Comments
Haradzieniec
That is a temporary table. Isn't it possible to write a query without adding additional columns, ids and primary keys?
Vargan
Sure, you can use rownumber as column id, in the inner query
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;
2 Comments
Haradzieniec
Create table _mytable select distinct mycolumn from mytable; worked fine for created table _mytable. Thank you very much for your help. Can anyone write a query without creating a new table?
kstevens715
The rest of the sql I wrote does the job of deleting and inserting back to mytable which gives the desired result. It also removes the temp table.
