Search code examples
mysqlsql-deletemysql-error-1093

Can't specify target table for update, delete query in mysql

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?

Solution

  • 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.