Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Mysql Error1093 error

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Scene demonstration: mysql> create table test (id int,name varchar(12),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 optimizer_switch system variable to force the subquery to be materialized into a temporary table, which effectively causes it to be a different table from the one modified. See Section 9.2.2.3, "Optimizing Derived Tables and View References".

MySQL does not support querying and updating the same table in a statement.

Solution 1: Change to SQL

For example, using temporary tables

mysql> update test set name='aaaa' where id in (select id from (select id from test)c);Query OK, 2 rows affected (0.02 sec)Rows matched: 2 Changed: 2 Warnings: 0

Solution 2:

This can be resolved by setting the derived_merge parameter of optimizer_switch.

Note: Oracle does not have this problem.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report