MySQL Error 1093 - Can't specify target table for update in FROM clause

mysql在update或者delete的where语句中使用子查询时报错“MySQL Error 1093 - Can't specify target table for update in FROM clause”

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category 
      INNER JOIN story_category ON category_id=category.id);

根据stackoverflow的帖子，将子查询改为join方式之后就可以了

UPDATE tbl AS a
  INNER JOIN tbl AS b ON ....
  SET a.col = b.col

另外如果不考虑性能非要用子查询，可以这样写

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

参考文章：http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

© 2016, 新之助meow. 原创文章转载请注明： 转载自http://www.xinmeow.com