查询同时修改同一张表问题.
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%');
update lock_table set locking=1,exec_time=now(),lock_num=lock_num+1
where com_uid=#com_uid# and shop_uid=#shop_uid# and lock_type=#lock_type#
and not exists(
select count(*) from lock_table where com_uid=#com_uid# and locking= 1 and lock_type in
<iterate open="(" close=")" conjunction="," property="types">
#types[]#
</iterate>
)
update lock_table s,(
select count(*) num from lock_table where com_uid=#com_uid# and locking= 1 and lock_type in
<iterate open="(" close=")" conjunction="," property="types">
#types[]#
</iterate>
) n
set s.locking=1,s.exec_time=now(),s.lock_num=s.lock_num+1
where s.com_uid=#com_uid# and s.shop_uid=#shop_uid# and s.lock_type=#lock_type#
and n.num=0
相关推荐
主要介绍了mysql中You can’t specify target table for update in FROM clause错误解决方法,需要的朋友可以参考下
最近在工作中遇到了一个mysql错误提示1093:You can’t specify target table for update in FROM clause,后来通过查找相关的资料解决了这个问题,现在将解决的方法分享给大家,有需要的朋友们可以参考借鉴,下面来...
1093 – You can’t specify target table ‘t’ for update in FROM clause, Time: 0 前情提示: Mac OS10.14+MySQL8.0.18; centOS6.8final+MySQL5.6; 解决: 方法一:多嵌套一层。多来一层子查询 以上SQL修改...
。。。
...
mysql 一个较特殊的问题:You can't specify target table 'wms_cabinet_form' for update in F
mysql 语句如下: update wms_cabinet_form set cabf_enabled=0 where cabf_id in ( SELECT wms_cabinet_form.cabf_id FROM wms_cabinet_form Inner Join wms_cabinet ON wms_cabinet_form.cabf_cab_id = wms_...
The foreign key in a table T1 _____ the same _____ as the corresponding primary key in table T2. must have, name need not have, name must have, domain (a) I, II, and III (b) I and II (c) ...
在平常的项目中,经常会碰到这样的问题:我需要在一张标中同时更新和查询出来的...结果却报错,报错信息为:You can't specify target table 'tb_test' for update in FROM clause,不能在同一语句中update,select同
If you don’t know the table owner or don’t want to enter it you can just enter a period. The idea of a custom autocomplete list is new. It gives you the ability to list anything you want. What ...
You can now update invisible columns in a result set. The "Save Windows state" preference now also works for SQL Windows. The vertical splitter position is now preserved if manually moved. The EXCEL=...
You can also specify the /logmsg switch to pass a predefined log message to the commit dialog. Or, if you don't want to pass the log message on the command line, use /logmsgfile:path, where ...
structure data from PDB files can now be used, and are stored in a database for lookup later dissect structures form can now show a list of known structures (pdb, mono, ...) Added a "revert to saved ...
If you currently work with VCLZip 2.X with TBlobStreams or some other type of streams, you can either define your own TkpBlobStream for instance which inherits from TkpHugeStream, or use the ...
and which you don't want SQL Assistant to treat as keywords, for example, the default configuration includes ID and Name, names, you can add your own. "Show Keys and Indexed Columns" is preset for ...
Download the 2013 v1 update to access the new TileControl to achieve a Windows 8 look and feel in your desktop apps as well as feature enhancements for Reports, Editor, and Scheduler. See the what's ...
Download the 2013 v1 update to access the new TileControl to achieve a Windows 8 look and feel in your desktop apps as well as feature enhancements for Reports, Editor, and Scheduler. See the what's ...
-Filter: in the admin page you can specify a field to act as a filter. A drop down will be populated with Distinct values from that Field. -Sorting: users can click on any column header to sort by ...