ERROR 1093 (HY000): You can't specify target table 'A' for update in FROM clause 对这个错误mysql提供的信息是
上面是目前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
遇到这种情况是可以这样
比如
mysql> update test1 as t1 ,test2 as t2
-> set t1.street=t2.address
-> where t1.id=t2.test_id and t1.id in(select id from test1);
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
mysql> create table t3 as select id as id from test1;
Query OK, 3 rows affected (0.03 sec)
mysql> update test1 as t1 ,test2 as t2
-> set t1.street=t2.address
->where t1.id=t2.test_id and t1.id in(select id from t3);
mysql中修改字段的长度是利用modify,change
alter table tbl_name modify 【column】 col_name column_type(长度);
或者为:
alter table tbl_name change 【column】 col_name col_name varchar(41);
如果仅仅只是修改字段的类型或长度:
可以直接使用modify
如果想要同时改变字段名称和类型
使用change
mysql 去重可以使用distinct關鍵字 select distinct name from testUser;
分享到:
相关推荐
主要介绍了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_...
在平常的项目中,经常会碰到这样的问题:我需要在一张标中同时更新和查询出来的...结果却报错,报错信息为:You can't specify target table 'tb_test' for update in FROM clause,不能在同一语句中update,select同
(b) the name of the table, the names of the table's attributes, the data types of the table's attributes, the formats of the table's attributes, and the maximum number of rows that the table can have...
You can specify for how many concurrent threads these steps will be executed and which Oracle accounts should be used. The performance results can be viewed in real-time as text, bar chart, or line ...
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 ...
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 ...
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 ...
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 ...
o You can now specify wildcards (For example: nir*.doc, abc???.txt) in the include/exclude filename option. * Version 2.51 o Added option to choose the file size display unit: Bytes, KB, or MB. ...
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 ...