我一直得到这个错误与这个删除。Mysql版本为5.0.77。我想不出还有什么别的问题。我甚至创建了一个临时表,以避免在select中修改表的问题。(
ERROR 1064(42000):你的SQL语法有错误;检查与MySQL服务器版本对应的手册,以便在'WHERE e.eid IN '附近使用正确的语法(选择*查询idFROM' at line 3
用下面的delete
DELETE
FROM emp2 e
WHERE e.eid IN
(SELECT *
FROM (SELECT w.eid
FROM works2 w,
emp2 e2,
dept2 d
WHERE w.did = d.did
AND d.managerid = e2.eid
AND e.salary > e2.salary
)
AS temp1
);
尝试从要删除的表中删除别名:
DELETE FROM emp2
WHERE eid IN (SELECT ...
这可能行得通:
DELETE e
FROM emp2 e
JOIN works2 w
ON w.eid = e.eid
JOIN dept2 d
ON w.did = d.did
JOIN emp2 e2
ON d.managerid = e2.eid
WHERE e.salary > e2.salary
或EXISTS
版本:
DELETE FROM emp2 e
WHERE EXISTS
( SELECT *
FROM works2 w,
emp2 e2,
dept2 d
WHERE w.did = d.did
AND d.managerid = e2.eid
AND e.salary > e2.salary
AND w.eid = e.eid
)
你到底想删除什么?