insert delete 操作是不允许使用表别名的。
update where条件中不允许自连接
update test set id=1 where pid in(select id from test where pid=12); 是不允许的。
解决办法：
update `a` as `c1`, `a` as `c2` set `c1`.`id` = 0
where `c1`.`id` <> `c2`.`sid`
MySQL ERROR 1093 (HY000): You can't specify target table 'forum_members'
SQL:
update forum_members
set is_multi = 1
where M_NAME IN(
select M_NAME from forum_members
group by M_NAME
having count(*) >1
)
该号令
1）在phpMyAdmin 中传：
#1093 - You can't specify target table 'forum_members' for update in FROM clause
2）在MySQL 号令列东西中传回：
ERROR 1093 (HY000): You can't specify target table 'forum_members' for update in FROM clause
原因：
mysql对子查询的支持是比力单薄的 ，就是说 update的where语句中不能子查询
在MySQL手册中的错误代码:
错误：1093 SQLSTATE: HY000 (ER_UPDATE_TABLE_USED)
动静：不能在FROM子句中制定要更新的方针表'%s'。
Mysql手册中的相干描写：
一般而言，不能更改表，并从子查询内的相同表进行选择。
例如，该限定合用于具有下述形式的语句：
DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
例外：如果为FROM子句中更改的表施用子查询，前述禁令将不再合用。
例如：
UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...)
AS _t ...);
禁令在此不合用，这是由于FROM中的子查询已被详细化为临时表，因此 “t”中的相干行已在满足“t”前提的环境下、在更新时当选中。
English：
· In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:
DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:
UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
Here the prohibition does not apply because a subquery in the FROM clause is materialized as a temporary table, so the relevant rows in t have a
解决：
依据手册将该号令改成：
UPDATE forum_members AS A
INNER JOIN (
SELECT M_NAME
FROM forum_members
GROUP BY M_NAME
HAVING count( * ) >1
) AS B _disibledevent=>
SET A.is_multi =1
最新评论