MySQL 中 delete 语句的子查询限制

转载自：http://www.cnblogs.com/dwarcheng/p/5887207.html

场景一：

DELETE FROM test 
WHERE
(A , B , C , D) IN(
    SELECT
        s.A ,
        s.B ,
        s.C ,
        s.D
    FROM
        test s
    GROUP BY
        s.A ,
        s.B ,
        s.C ,
        s.D
    HAVING
        COUNT(*) > 1
)
AND id NOT IN(
    SELECT MIN(d.id) FROM test d 
    GROUP BY d.A ,d.B ,d.C ,d.D 
    HAVING COUNT(*) > 1
)

提示：Error : You can’t specify target table ‘test’ for update in FROM clause

描述: 如果子查询的 from 子句和更新、删除对象使用同一张表，会出现上述错误。

解决方法: 通过给 from 子句中的结果集起别名。

场景二

DELETE test t where t.id = '002'

提示：Error : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘t where t.id = ‘002” at line 1

描述: delete from table 这样的句子中 table 不能使用别名。

解决方法：去掉别名