You can't specify target table for update in FROM

You can't specify target table for update in FROM clause

mysql> Update EACONTACTGROUPS A
SET GROUPNAME=(Select CONCAT(B.GROUPNAME,'-',A.GROUPNAME) FROM EACONTACTGROUPS B Where B.CORPID=A.CORPID AND B.USERID=A.USERID AND B.GROUPID=A.PARENTGROUPID)
Where A.PARENTGROUPID IS NOT NULL;
ERROR 1093 (HY000): You can't specify target table 'A' for update in FROM clause
上面是目前MYSQL5.0仍然有的限制，文档中说：
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 already been selected by the time the update to t takes place.

依据文档，改成下面的样子就行了：
mysql> Update EACONTACTGROUPS A
SET GROUPNAME=(Select CONCAT(B.GROUPNAME,'-',A.GROUPNAME) FROM ( Select GROUPNAME,CORPID,USERID,GROUPID,PARENTGROUPID FROM EACONTACTGROUPS) B
Where B.CORPID=A.CORPID AND B.USERID=A.USERID AND B.GROUPID=A.PARENTGROUPID)
Where A.PARENTGROUPID IS NOT NULL;
Query OK, 16 rows affected (0.01 sec)
Rows matched: 16 Changed: 16 Warnings: 0

文章来自: 本站原创
引用通告: 查看所有引用 | 我要引用此文章
Tags:
相关日志: