mysql insert 别名_mysql 不能使用别名

MySQL版本5.0

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 ON A.M_NAME = B.M_NAME

SET A.is_multi =1

分享到：

2010-10-09 12:10

浏览 2050

分类:数据库

评论