MySQL错误1093 - 无法在FROM子句中指定更新的目标表
我在我的数据库中有一个表story_category
,包含损坏的条目。接下来的查询将返回损坏的项目:MySQL错误1093 - 无法在FROM子句中指定更新的目标表
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
我如何克服这个问题?
更新:这个答案涵盖了一般的错误分类。有关如何最好地处理业务方案的确切查询更具体的答案,请参阅其他回答这个问题
在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,originally published at Nabble,在这里作了释义和扩展。
Upvoted这个答案,因为我不得不删除项目,并不能从另一个表中获取信息,必须从同一个表中查询。由于这是弹出的顶部,而谷歌搜索错误,我得到了这将是最适合我的答案,很多人试图从同一个表subquerieing更新。 – HMR 2012-12-12 03:36:56
@Cheekysoft,为什么不把值保存到变量中呢? – Pacerier 2015-02-24 03:47:27
请注意,从[MySQL 5.7.6 on](http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html)中,优化器可以优化子 - 查询并仍然给你的错误,除非你`SET optimizer_switch ='derived_merge = off';`:-( – 2015-11-06 15:36:41
您可以将所需行的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;
它速度快。数据越大越好。
而且你刚刚失去了所有外键,也许你还有一些级联删除 – Walf 2014-12-09 06:48:49
您的子查询中的inner join
是不必要的。看起来您希望删除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);
这是我做了更新优先级列值由1如果是> = 1的表格,并在其WHERE子句在同一个表上使用子查询以确保至少有一行包含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);
我知道这有点丑,但它确实很好。
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
)
NexusRex与来自同一个表连接删除提供very good solution。
如果你这样做:
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id AS cid FROM category
INNER JOIN story_category ON category_id=category.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
)
它会做正确的事!
说明:查询优化器对所述第一查询一个derived merge optimization(从而导致它失败,错误),但第二个查询不符合的衍生合并优化因此优化器被强制为首先执行子查询。
,近来我在同一个表,我没有像下面更新记录:
UPDATE skills AS s, (SELECT id FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development'
WHERE s.id = p.id;
根据 the Mysql UPDATE Syntax由@CheekySoft相连,它的权利在底部说。
Currently, you cannot update a table and select from the same table in a subquery.
我想你是从store_category中删除,同时仍然从工会中选择它。
尝试将Select语句的结果保存在单独的变量中,然后将其用于删除查询。
如果你不能做到
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 LEFT JOIN (SELECT category.id FROM category) cat ON story_category.id = cat.id WHERE cat.id IS NULL
当您在子查询中引用父查询表时,最简单的方法是使用表别名。
例子:
insert into xxx_tab (trans_id) values ((select max(trans_id)+1 from xxx_tab));
将其更改为:
insert into xxx_tab (trans_id) values ((select max(P.trans_id)+1 from xxx_tab P));
试试这个
DELETE FROM story_category
WHERE category_id NOT IN (
SELECT DISTINCT category.id
FROM (SELECT * FROM STORY_CATEGORY) sc;
相关:http://*.com/a/14302701/238419 – 2013-02-22 18:10:06
好像MySQL错误跟踪器中的功能请求位于:[无法更新表并从子查询中的同一个表中选择](https://bugs.mys ql.com/bug.php?id=23353) – 2017-12-30 06:32:26