問題描述
我的數(shù)據(jù)庫中有一個表 story_category，其中包含損壞的條目.下一個查詢返回損壞的條目:
I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries:
SELECT *
FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category INNER JOIN
story_category ON category_id=category.id);
我試圖刪除它們執(zhí)行:
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category
INNER JOIN story_category ON category_id=category.id);
但我收到下一個錯誤:
#1093 - 不能在 FROM 子句中為更新指定目標表story_category"
#1093 - You can't specify target table 'story_category' for update in FROM clause
我怎樣才能克服這個問題?
How can I overcome this?
推薦答案
更新:此答案涵蓋一般錯誤分類.有關如何最好地處理 OP 的確切查詢的更具體答案，請參閱此問題的其他答案
在 MySQL 中，您不能修改在 SELECT 部分使用的同一個表.
此行為記錄在:http://dev.mysql.com/doc/refman/5.6/en/update.html
In MySQL, you can't modify the same table which you use in the SELECT part.
This behaviour is documented at:
http://dev.mysql.com/doc/refman/5.6/en/update.html
也許您可以將表格加入到自己的表格中
如果邏輯足夠簡單，可以重新調整查詢，則丟失子查詢并將表連接到自身，采用適當?shù)倪x擇標準.這將導致 MySQL 將表視為兩種不同的事物，從而允許進行破壞性更改.
If the logic is simple enough to re-shape the query, lose the subquery and join the table to itself, employing appropriate selection criteria. This will cause MySQL to see the table as two different things, allowing destructive changes to go ahead.
UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col
或者，嘗試將子查詢更深入地嵌套到 from 子句中...
如果您絕對需要子查詢，有一個解決方法，但它是丑陋的原因有很多，包括性能:
If you absolutely need the subquery, there's a workaround, but it's ugly for several reasons, including performance:
UPDATE tbl SET col = (
SELECT ... FROM (SELECT.... FROM) AS x);
FROM 子句中的嵌套子查詢創(chuàng)建了一個隱式臨時表，因此它不算作您正在更新的同一個表.
The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're updating.
...但要注意查詢優(yōu)化器
但是，請注意 MySQL5.7.6 及以后，優(yōu)化器可能會優(yōu)化出子查詢，但仍然會出現(xiàn)錯誤.幸運的是，optimizer_switch 變量可用于關閉此行為；盡管我不建議將其作為短期解決方案或一次性小任務.
However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks.
SET optimizer_switch = 'derived_merge=off';
感謝 Peter V. M?rch 在評論中提供的建議.
Thanks to Peter V. M?rch for this advice in the comments.
示例技術來自 Baron Schwartz，最初發(fā)表于 Nabble，在此處進行了釋義和擴展.
Example technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.
這篇關于MySQL 錯誤 1093 - 無法在 FROM 子句中指定更新的目標表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！