MYSQL 1093 - You can't specify target table '' for update in FROM clause

        有一次由于不太熟悉业务，忘了加唯一索引，导致数据库里不少重复数据。为了删除重复数据，编写下面的SQL语句，但是执行完后却报了如标题的错误。原因是：MYSQL不允许在进行子查询的同时删除原表数据。

DELETE FROM table1
    where order_id in
        (select order_id from table1 group by order_id having count(order_id) > 1)
    and id not in
        (select min(id) from table1 group by order_id having count(order_id)>1);

        其中的一个解决办法如下，就是在将查询结果作为一个结果集，再从这个结果集中取数据，然后保留最小id的数据。SQL如下(第2,3,4,9,10为新添加的)。

delete from table1
  where id in (
        select id from (
            select id from table1
                where order_id in
                (select order_id from table1 group by order_id having count(*) > 1)
                and id not in
                (select min(id) from table1 group by order_id having count(*)>1)
        ) as t
    );

       上面的sql给DBA看到后，立马就给喷了，说这样执行太低，于是修改成下面这样：

delete a from table1 a ,
	(select min(id) as ms ,order_id from table1 group by order_id having count(*)>1) b
        where a.order_id=b.order_id and a.id>b.ms

       面壁去。。。。。。