【MySQL】ERROR 1093 You can

神諭丶發表於2015-12-17

【問題描述】:

Error: 
1093 SQLSTATE: HY000 ()

Message: You can't specify target table '%s' for update in FROM clause


  1. mysql> SHOW ERRORS;
  2. +-------+------+----------------------------------------------------------------------------------------+
  3. | Level | Code | Message                                                                                |
  4. +-------+------+----------------------------------------------------------------------------------------+
  5. | Error | 1093 | You can't specify target table 'table_name' for update in FROM clause                  |       
  6. +-------+------+----------------------------------------------------------------------------------------+
  7. 1 row in set (0.02 sec)


常見的一個ERROR,在UPDATE時會出現,故DELETE時也有可能出現該問題。

大致意思是,不能根據某張表裡SELECT出來的條件去UPDATE(DELETE)。

比如如下語句:
  1. DELETE FROM table_name
  2. WHERE id IN 
  3. (SELECT id
  4. FROM table_name 
  5. GROUP BY company_id
  6. HAVING COUNT(1)>1);

【解決方案】:

先將SELECT出來的結果集放入臨時表,再從臨時表取出條件
比如:

  1. DELETE FROM table_name
  2. WHERE id IN
  3. (SELECT a.id from
  4.     (SELECT id
  5.     FROM table_name
  6.     GROUP BY company_id
  7.     HAVING COUNT(1)>1) a
  8.     );


作者公眾號(持續更新)

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29773961/viewspace-1873900/,如需轉載,請註明出處,否則將追究法律責任。

相關文章