我的数据库中有一个表story_category，其中包含损坏的条目。下一个查询将返回损坏的条目：
SELECT *
FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category INNER JOIN
story_category ON category_id=category.id);
我试图删除它们执行：
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”
我如何克服这一问题？
当前回答
如果你做不到
UPDATE table SET a=value WHERE x IN
(SELECT x FROM table WHERE condition);
因为它是同一张桌子，所以你可以一边玩一边做：
UPDATE table SET a=value WHERE x IN
(SELECT * FROM (SELECT x FROM table WHERE condition) as t)
[更新或删除或其他]
其他回答
试试这个
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM (SELECT * FROM STORY_CATEGORY) sc;
最近，我不得不更新同一个表中的记录，如下所示：
UPDATE skills AS s, (SELECT id FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development'
WHERE s.id = p.id;
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT cid FROM (
SELECT DISTINCT category.id AS cid FROM category INNER JOIN story_category ON category_id=category.id
) AS c
)
这个查询怎么样？希望有帮助
DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL
如果你做不到
UPDATE table SET a=value WHERE x IN
(SELECT x FROM table WHERE condition);
因为它是同一张桌子，所以你可以一边玩一边做：
UPDATE table SET a=value WHERE x IN
(SELECT * FROM (SELECT x FROM table WHERE condition) as t)
[更新或删除或其他]
