You can't specify target table 'A' for update in FROM clause
来源:互联网 发布:mac能玩魔兽世界吗 编辑:程序博客网 时间:2024/06/05 09:38
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%');
- You can't specify target table 'A' for update in FROM clause
- MySQL:You can’t specify target table ‘A’ for update in FROM clause
- You can't specify target table 'A' for update in FROM clause
- MySQL:You can’t specify target table ‘A’ for update in FROM clause
- mysql update语句问题:You can't specify target table 'user' for update in FROM clause
- You can't specify target table 'table name' for update in FROM clause
- MySql报错: You can't specify target table 'table name' for update in FROM clause
- 【MySQL】1093 You can't specify target table 'table name' for update in FROM clause
- [Err] 1093 - You can't specify target table 'table' for update in FROM clause处理
- You can't specify target table 't' for update in FROM clause
- Mysql - You can't specify target table 'dataproduct' for update in FROM clause
- You can't specify target table 'wms_cabinet_form' for update in FROM clause
- MySQL报错“ you can't specify the target table for ‘update’ in from clause”
- You can't specify target table 'sc1' for update in FROM clause
- [Err] 1093 - You can't specify target table 's' for update in FROM clause
- mysql中You can't specify target table for update in FROM clause错误
- You can't specify target table 'sdb_goods' for update in FROM clause
- MYSQL 临时表 #1093 - You can't specify target table 'dealer_admin' for update in FROM clause
- 2012年度最佳jQuery插件揭晓
- base64 中文加密和解码
- 对象工具类
- 搭建自己的SIP服务器:开源sip服务器opensips的搭建及终端TwInkle的使用
- Subset
- You can't specify target table 'A' for update in FROM clause
- 设计模式 - 策略模式
- EL表达式无法获取boolean类型变量值
- INS-20702 Reported during 11gR2 Installation on getSharedPartitionListCVU [ID 1056195.1]
- 优化Jquery,加快网页加载速度
- Android参考文档的使用
- HibernateDaoSupport详解
- 判断IE浏览器版本
- 计算某一个文件的行数