Mysql: Table 'name' is specified twice, both as a target for 'UPDATE' and as a separate source for data

標籤：

mysql在執行刪除、更新的時候，可能會出現這樣的錯誤，比如你寫了類似下面的sql：

UPDATE MYTABLE SET COL=COL+1 WHERE ID IN (SELECT ID FROM MYTABLE WHERE OTHERCOL=0)

or

DELETE FROM MYTABLE WHERE ID IN (SELECT ID FROM MYTABLE WHERE OTHERCOL=0)

這個時候就會出現Table ‘xxx‘ is specified twice這樣的錯誤，不知道mysql為什麼會做這樣的凍結，知道的大神請給個連結。

要解決這個問題很簡單，既然table ‘xxx‘不能放到子查詢裡面，那就做一個緩衝表唄。

DELETE FROM MYTABLE WHERE ID IN (SELECT * FROM (SELECT ID FROM MYTABLE WHERE OTHERCOL=0) AS TEMP)

　這樣就沒有問題了。

Mysql: Table 'name' is specified twice, both as a target for 'UPDATE' and as a separate source for data