Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the differences between oracle and mysql about related updates. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Mysql error ERROR 1093 (HY000): You can't specify target table 'a'for update in FROM clause

Today, some abnormal data on the website needs to be corrected. The specific operation is to associate and update the publish_date field in a table to the up_date field in the same table. Our data is first stored in oracle, and then passed to mysql. All abnormal data exists in oracle and mysql.

First correct in oracle:

SQL > update infoservice.t_publish_zbxx a set a publisher date = (select b.up_date from infoservice.t_publish_zbxx b where a.record_id=b.record_id) where a.publish_date > to_date ('2017-10-15 copyright mmmaedd')

Then corresponding to rewrite the mysql corresponding table and the corresponding sql, unexpectedly reported an error.

Mysql > update v_publish_info a set a.publisher date = (select b.up_date from v_publish_info b where a.id=b.id) where a.publish_date > '2017-07-15'

ERROR 1093 (HY000): You can't specify target table 'a'for update in FROM clause

Solution:

Mysql > update v_publish_info a minute publisher info b set a.publish_date=b.up_date where a.id=b.id and a.publish_date > '2017-07-15'

Or

1, query the columns of data to be updated as a third-party table, and then filter the updates.

2, create a new temporary table to save the queried data, and then filter the updates. Finally, delete the temporary table.

The details are as follows:

Create table liuwenhe.publish_date_temp as select id, publish_date,up_date from info.v_publish_info where publish_date > '2017-07-15'

Update info.v_publish_info a set a.publishdatedate = (select b.up_date from liuwenhe.publish_date_temp b where a.id=b.id) where a.publish_date > '2017-07-15'

In order to prevent the problem of matching mismatch and updating to empty, you can add the exists condition.

Update info.v_publish_info a set a.publishdatedate = (select b.up_date from liuwenhe.publish_date_temp b where a.id=b.id) where a.publish_date > '2017-07-15' and exists (select b.up_date from liuwenhe.publish_date_temp b where a.id=b.id)

Here are some experiments on association updates:

Mysql:

1. Success

Mysql > update liuwenhe.publish_date_20170715 a set a publisher date = (select b.up_date from info.v_publish_info b where a.id=b.id) where a.publish_date > '2017-07-15' and exists (select b.up_date from info.v_publish_info b where a.id=b.id)

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0 Changed: 0 Warnings: 0

two。 Fail

Mysql > update liuwenhe.publish_date_20170715 a set a.publisher date = (select b.up_date from liuwenhe.publish_date_20170715 b where a.id=b.id) where a.publish_date > '2017-07-15'

ERROR 1093 (HY000): You can't specify target table 'a'for update in FROM clause

3. Success

Mysql > update liuwenhe.publish_date_20170715 a department liuwenhe.publishkeeper datebook 20170715b set a.publish_date=b.in_date where a.id=b.id and a.publish_date > '2017-07-15'

Query OK, 0 rows affected (0.01 sec)

Rows matched: 0 Changed: 0 Warnings: 0

Oracle:

4. Fail

SQL > update liuwenhe.top_80 a minute infoservice.tasking memberships info b set a.login_id=b.login_id where a.member_id=b.record_id

Update liuwenhe.top_80 a,infoservice.t_member_info b set a.login_id=b.login_id where a.member_id=b.record_id

ERROR at line 1:

ORA-00971: missing SET keyword

5. Failed:

SQL > update liuwenhe.top_80 a dint liuwenhe.topcoat 80b set a.login_id=b.login_id where a.member_id=b.member_id

Update liuwenhe.top_80 a,liuwenhe.top_80 b set a.login_id=b.login_id where a.member_id=b.member_id

ERROR at line 1:

ORA-00971: missing SET keyword

6. Success

SQL > update liuwenhe.top_80 a set a.loginroomid= (select b.login_id from infoservice.t_member_info b where a.member_id=b.record_id)

Through the comparison of experiment 1 and 2, we can know that it is not possible to update the same table in mysql, but in oracle, and in experiment 4 and 5, we can know that update a set b can not be used in oracle. As you can see in experiment 3, mysql can use update a set a. To associate the update table with statements such as

These are the differences between oracle and mysql about association updates shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.