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

Today, in an attempt to update a table with a subquery, the following error was received: ERROR 1093 (HY000): You can't specify target table 'vmemorymembermemorinfo' for update in FROM clause: the specific sql executed is as follows: MySQL [meminfo] > update v_member_info set cust_right_group=0 where id in (select id from v_member_info where pay_end_date0) ERROR 1093 (HY000): You can't specify target table 'vmemorymemberroominfo' for update in FROM clause is originally mysql. When the original table is in update, the original table cannot appear in the subquery at the first layer after where. Solution: two rewriting methods 1) update MySQL [meminfo] > update v_member_info as a, (select id,cust_right_group from v_member_info where pay_end_date0) as b set a.cust_right_group=0 where a.id=b.id in join mode Query OK, 288 rows affected (2.35 sec) Rows matched: 288 Changed: 288 Warnings: 02) or subquery MySQL [meminfo] > update v_member_info set cust_right_group=0 where id in (select id from (select id from v_member_info where pay_end_date0) a) Summary: there is a difference between Oracle and MySQL. When MySQL is in update, the original table cannot appear in the subquery at the first layer behind where. As for the performance of the two rewrites, it depends on the specific business and the amount of data.

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.