MySQL-1093 exception-You can ' t specify target table ' t ' for update in FROM clause

There is a table that represents the region, and the table structure and data is probably the following table.

	name	parent_id
1	China
2	Guangdong	1
3	Guangzhou	2
	Liwan District	3
5	Yuexiu District	3
6	Fanyu District	3
7	Xiao Gu wai Jie dao	6

For the convenience of query, it is necessary to add a column of Parent_name to indicate the name of the superior area (although it does not conform to the third paradigm, transmission dependence, but sometimes for business feasibility, convenience, can be considered according to the actual situation)

Id	NAME	parent_id	Parent_name
1	China
2	Guangdong Province	1
3	Guangzhou	2
4	Liwan District	3
5	Yuexiu district	3
6	Panyu	3
7	Xiao Gu wai Jie dao	6

The DDL, DML, table:

-- ------------------------------Table structure for ' T_area '-- ----------------------------CREATE TABLE' T_area ' (' ID ')int( One) not NULLauto_increment, ' name 'varchar( the)DEFAULT NULL, ' parent_id 'int( One)DEFAULT NULL, ' parent_name 'varchar( the)DEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=InnoDB auto_increment=8 DEFAULTCHARSET=UTF8;-- ------------------------------Records of T_area-- ----------------------------INSERT  into' T_area 'VALUES('1','China',NULL,NULL);INSERT  into' T_area 'VALUES('2','Guangdong province','1',NULL);INSERT  into' T_area 'VALUES('3','Guangzhou','2',NULL);INSERT  into' T_area 'VALUES('4','Liwan District','3',NULL);INSERT  into' T_area 'VALUES('5','Yuexiu District','3',NULL);INSERT  into' T_area 'VALUES('6','Fanyu District','3',NULL);INSERT  into' T_area 'VALUES('7','Xiao Gu wai Jie dao','6',NULL);

View Code

At this point, you need to update the Parent_name data based on the existing information, you have the following sql:

/**/updateset= (selectfromwhere  = t2.id);

View Code

Quote "1093-you can ' t specify target table ' t ' for update in FROM clause" exception. It means that you can't specify the updated target table in the FROM clause (English is not good, even if you know each word, the string up is not ...) ）

As described in the document "Currently, you cannot update a table and select from the same table in a subquery.", see HTTP://DEV.MYSQL.COM/DOC/REFMA N/5.5/en/update.html.

Do not know why MySQL is not allowed to do so, guess, may be worried about the updated table and query table for the same table will have nested recursion? Or is it a matter of worrying about efficiency?

If the table is nested in a layer, that is, "(SELECT * from T_area) St" to derive a temporary result set, that is, no error, but this performance is poor, only for the small amount of data. (See this discussion: Http://stackoverflow.com/questions/17742214/you-cant-specify-target-table-name-for-update-in-from-clause).

The following changes are followed:

-- OK Update Set = (Select fromwhere= t2.id);

View Code

Specifically for this need, a simpler way, seemingly can also:

Update Set = where = t2.id;

View Code

MySQL-1093 exception-You can ' t specify target table ' t ' for update in FROM clause