MySQL Error1093 error

Scene demo:mysql> CREATE TABLE test (ID int,name varchar (), primary key (ID)); Query OK, 0 rows affected (0.04 sec) mysql> insert into test values (1, ' TTT '); Query OK, 1 row Affected (0.00 sec) mysql> INSERT INTO test values (2, ' TTT '); Query OK, 1 row Affected (0.00 sec) Mysql> commit; Query OK, 0 rows Affected (0.00 sec) mysql> Update test set name= ' AAAA ' where ID in (select ID from test); ERROR 1093 (HY000): You can ' t specify target table ' test ' for update in FROM clause

Official Note:

error:1093 sqlstate:hy000 (er_update_table_used)

Message:you can ' t specify target table '%s ' for update in FROM clause

This error occurs for attempts to select from and modify the same table within a single statement. If The Select attempt occurs within a derived table, you can avoid this error by setting the Derived_merge flag of the OPT Imizer_switch system variable to force the subquery to being materialized into a temporary table, which effectively causes it To is a different table from the one modified. See section 9.2.2.3, "Optimizing Derived Tables and View References".

MySQL does not support, in a statement on the same table, the first query and then update the operation.

Workaround 1: Change to SQL

For example, using temporary tables

mysql> Update test set name= ' AAAA ' where ID in (the Select ID from (SELECT ID from test) c); Query OK, 2 rows affected (0.02 sec) rows Matched:2 changed:2 warnings:0

Workaround 2:

The official said: can be resolved by setting the Derived_merge parameter of the Optimizer_switch.

Note: Oracle does not have this problem

This article is from the "Corasql" blog, make sure to keep this source http://corasql.blog.51cto.com/5908329/1912236

MySQL Error1093 error