我的数据库中有一个带有损坏条目的表" 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 - You can't specify target table 'story_category' for update in FROM clause

我该如何克服呢？

解决方案

回答

更新：此答案涵盖了常规错误分类。有关如何最好地处理OP的确切查询的更具体的答案，请参阅此问题的其他答案

在MySQL中，我们不能修改SELECT部分​​中使用的同一表。
在以下位置记录了此行为：
http://dev.mysql.com/doc/refman/5.6/en/update.html

也许我们可以将桌子加入自己

如果逻辑足够简单以重新构造查询，则丢失子查询并使用适当的选择条件将表连接到自身。这将导致MySQL将表视为两种不同的事物，从而允许进行破坏性的更改。

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

或者，尝试将子查询更深地嵌套到from子句中。

如果我们绝对需要子查询，则有一种解决方法，但这是
丑陋的原因有几个，包括性能：

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

FROM子句中的嵌套子查询创建一个隐式临时
表，因此它不算作我们要更新的表。

...但是要注意查询优化器

但是，请注意，从MySQL 5.7.6及更高版本开始，优化器可能会优化子查询，但仍然会给我们错误。幸运的是，optimizer_switch变量可以用来关闭这种行为。尽管我不建议我们将其作为短期解决方案或者小型一次性任务来完成。

SET optimizer_switch = 'derived_merge=off';

感谢Peter V. M？rch在评论中提供的建议。

示例技术来自史瓦兹男爵(Baron Schwartz)，最初出版于纳布尔(Nabble)，在此释义并扩展。

回答

我们可以将所需行的ID插入到临时表中，然后删除该表中找到的所有行。

这可能就是@Cheekysoft分两步进行操作的意思。

回答

如果某些事情不起作用，则从前门进来时，请走后门：

drop table if exists apples;
create table if not exists apples(variety char(10) primary key, price int);

insert into apples values('fuji', 5), ('gala', 6);

drop table if exists apples_new;
create table if not exists apples_new like apples;
insert into apples_new select * from apples;

update apples_new
    set price = (select price from apples where variety = 'gala')
    where variety = 'fuji';
rename table apples to apples_orig;
rename table apples_new to apples;
drop table apples_orig;

它很快。数据越大，效果越好。

回答

子查询中的"内部联接"是不必要的。我们似乎要删除" story_category"中的条目，其中" category_id"不在" category"表中。

做这个：

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category);

而不是：

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN
         story_category ON category_id=category.id);

回答

这是我为在表中及其表中的WHERE子句中> = 1时将Priority列值更新为1而使用同一表上的子查询所做的操作，以确保至少一行包含Priority = 1(因为执行更新时要检查的条件)：

UPDATE My_Table
SET Priority=Priority + 1
WHERE Priority >= 1
AND (SELECT TRUE FROM (SELECT * FROM My_Table WHERE Priority=1 LIMIT 1) as t);

我知道这有点丑陋，但效果确实不错。