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

今天網站有些資料出現異常，需要把出現異常的資料更正，具體操作是把一個表中的publish_date欄位關聯更新成同一個表中的up_date欄位,我們的資料是先存到oracle，然後透過到mysql中的，所有異常資料存在於oracle和mysql。

首先在oracle中更正：

SQL>update infoservice.t_publish_zbxx a set a.publish_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','yyyy-mm-dd')；

然後對應著改寫成mysql相應表和相應sql,居然報錯。。。。

mysql> update v_publish_info a set a.publish_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

解決辦法：

mysql> update v_publish_info a，v_publish_info b set a.publish_date=b.up_date where a.id=b.id and a.publish_date>'2017-07-15';

或者

1，把要更新的幾列資料查詢出來做為一個第三方表，然後篩選更新。

2，新建一個臨時表儲存查詢出的資料，然後篩選更新。最後刪除臨時表。

具體如下：

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.publish_date=(select b.up_date from liuwenhe.publish_date_temp b where a.id=b.id) where a.publish_date>'2017-07-15';

為了防止匹配不上，更新為空的問題，可以加上exists條件；

update info.v_publish_info a set a.publish_date=(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);

如下是關於關聯更新的一些實驗：

mysql：

1.成功

mysql> update liuwenhe.publish_date_20170715 a set a.publish_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

2.失敗

mysql> update liuwenhe.publish_date_20170715 a set a.publish_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.成功

mysql> update liuwenhe.publish_date_20170715 a,liuwenhe.publish_date_20170715 b 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.失敗

SQL> update liuwenhe.top_80 a,infoservice.t_member_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.失敗：

SQL> update liuwenhe.top_80 a,liuwenhe.top_80 b 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.成功

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

總結：透過實驗1和2比較可以知道，mysql中是不能關聯更新同一個表的，但是oracle中可以；實驗4和5可以知道oracle中不能使用update a,b set a.=b.之類的語句；實驗3可以知道，mysql可以使用update a,b set a.=b.之類的語句來關聯更新表；

oracle和mysql關於關聯更新的一些差別以及ERROR 1093

相關文章