我的数据库中有一个表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”
我如何克服这一问题？
当前回答
这个查询怎么样？希望有帮助
DELETE FROM story_category LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL
其他回答
子查询中的内部联接是不必要的。看起来您想要删除story_category中的条目，其中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);
执行以下操作：
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 cid FROM (
SELECT DISTINCT category.id AS cid FROM category INNER JOIN story_category ON category_id=category.id
) AS c
)
如果你做不到
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)
[更新或删除或其他]
如果从前门进来时有些东西不起作用，那么从后门走：
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中不存在于类别中的行。
以下是用于标识要删除的行的原始查询：
SELECT *
FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category INNER JOIN
story_category ON category_id=category.id
);
将NOT IN与JOIN原始表的子查询组合在一起似乎不太复杂。这可以用不存在和相关子查询以更直接的方式表示：
select sc.*
from story_category sc
where not exists (select 1 from category c where c.id = sc.category_id);
现在很容易将其转换为delete语句：
delete from story_category
where not exists (select 1 from category c where c.id = story_category.category_id);
这个quer可以在任何MySQL版本上运行，也可以在我知道的大多数其他数据库中运行。
DB Fiddle演示：
-- set-up
create table story_category(category_id int);
create table category (id int);
insert into story_category values (1), (2), (3), (4), (5);
insert into category values (4), (5), (6), (7);
-- your original query to identify offending rows
SELECT *
FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM category INNER JOIN
story_category ON category_id=category.id);
| category_id | | ----------: | | 1 | | 2 | | 3 |
-- a functionally-equivalent, simpler query for this
select sc.*
from story_category sc
where not exists (select 1 from category c where c.id = sc.category_id)
| category_id | | ----------: | | 1 | | 2 | | 3 |
-- the delete query
delete from story_category
where not exists (select 1 from category c where c.id = story_category.category_id);
-- outcome
select * from story_category;
| category_id | | ----------: | | 4 | | 5 |
