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;