bestlong 怕失憶論壇

標題: MySQL ERROR 1093 (HY000): You can't specify target table 'forum_members' [打印本頁]

作者: bestlong 時間: 2006-10-18 16:18 標題: MySQL ERROR 1093 (HY000): You can't specify target table 'forum_members'

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

update forum_members
set is_multi = 1
where M_NAME IN(
select M_NAME from forum_members
group by M_NAME
having count(*) >1
)

複製代碼

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

複製代碼

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

UPDATE forum_members AS A
INNER JOIN (
SELECT M_NAME
FROM forum_members
GROUP BY M_NAME
HAVING count( * ) >1
) AS B ON A.M_NAME = B.M_NAME
SET A.is_multi =1

複製代碼

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

作者: bestlong 時間: 2006-10-18 17:27

這個查詢命令可以執行

SELECT *
FROM forum_members
WHERE M_NAME IN (
SELECT M_NAME
FROM (
SELECT M_NAME, count( * )
FROM forum_members
GROUP BY M_NAME
HAVING count( * ) >1
) AS x
)

複製代碼

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

UPDATE forum_members
SET is_multi =1
WHERE M_NAME IN (
SELECT M_NAME
FROM (
SELECT M_NAME, count( * )
FROM forum_members
GROUP BY M_NAME
HAVING count( * ) >1
) AS x
)

複製代碼

歡迎光臨 bestlong 怕失憶論壇 (http://www.bestlong.idv.tw/)