	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

相關文章