I want to do what seems like a simple delete in mysql but am having some trouble. I want to do this:
delete from table where col_name not in (select distinct col_name from table);
This table does not have a unique key. When I try this, I get the error:
You can't specify target table for update in from clause; ErrorNr. 1093
mysql community 5.1
Is there a way i can save the output from this query ->
select distinct col_name from table;
into temp. table and use it in the delete query?
You must use an alias.
delete from table where col_name not in (select a.col_name from (select distinct col_name from table) a);
It should work.
Edit : sorry, I misunderstood the question, and only focus on the SQL Error. The probleme to delete duplicate line with no unique key isn't answered by the above request.