You can't specify target table 'A' 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

  注:今天写一个删除语句时找到的一点资料.解决了一个简单的删除语句.呵呵,以后注意了,

  原句:delete  from menu_item  where parent_id =(select menu_item_id from menu_item where menu_data like '%manageVendors%');

  修改后:delete  from menu_item  where parent_id =(select temp.menu_item_id from (select m.menu_item_id,m.menu_data from menu_item m) temp where temp.menu_data like '%manageVendors%');