MySQL ERROR 1093 (HY000): You can't specify target table 'forum_members'
在 MySQL 4.1.20 環境下對一個有 6 萬多筆紀錄的資料表執行更新命令如下
[code]update forum_members
set is_multi = 1
where M_NAME IN(
select M_NAME from forum_members
group by M_NAME
having count(*) >1
)[/code]
[code]在 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[/code]
這應該是不支援 SubQuery 的問題，改成下列命令處理
[code]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[/code]
影響列數: 1050 (查詢需時 349.5837 秒) 這個查詢命令可以執行
[code]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
)[/code]
但是這個更新命令卻是錯誤
[code]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
)[/code]
頁:
[1]