MySQL ERROR 1093 (HY000): You can't specify target table 'forum_members'

bestlong

在 MySQL 4.1.20 環境下
對一個有 6 萬多筆紀錄的資料表執行更新命令如下

1. update forum_members
   
2. set is_multi = 1
   
3. where M_NAME IN(
   
4. select M_NAME from forum_members
   
5. group by M_NAME
   
6. having count(*) >1
   
7. )

複製代碼

1. 在 phpMyAdmin 中傳回：
   
2. #1093 - You can't specify target table 'forum_members' for update in FROM clause
   
3. 
   
4. 在 MySQL 命令列工具中傳回：
   
5. ERROR 1093 (HY000): You can't specify target table 'forum_members' for update in FROM clause

複製代碼

這應該是不支援 SubQuery 的問題，改成下列命令處理

1. UPDATE forum_members AS A
   
2. INNER JOIN (
   
3. SELECT M_NAME
   
4. FROM forum_members
   
5. GROUP BY M_NAME
   
6. HAVING count( * ) >1
   
7. ) AS B ON A.M_NAME = B.M_NAME
   
8. SET A.is_multi =1

複製代碼

影響列數:  1050 (查詢需時 349.5837 秒)

bestlong

這個查詢命令可以執行

1. SELECT *
   
2. FROM forum_members
   
3. WHERE M_NAME IN (
   
4. SELECT M_NAME
   
5. FROM (
   
6. SELECT M_NAME, count( * )
   
7. FROM forum_members
   
8. GROUP BY M_NAME
   
9. HAVING count( * ) >1
   
10. ) AS x
    
11. )

複製代碼

但是這個更新命令卻是錯誤

1. UPDATE forum_members
   
2. SET is_multi =1
   
3. WHERE M_NAME IN (
   
4. SELECT M_NAME
   
5. FROM (
   
6. SELECT M_NAME, count( * )
   
7. FROM forum_members
   
8. GROUP BY M_NAME
   
9. HAVING count( * ) >1
   
10. ) AS x
    
11. )

複製代碼