问题描述
我在获取选择子查询以处理 UPDATE 时遇到问题.我正在尝试以下方法:
I have an issue with getting select sub-queries to work on an UPDATE. I'm trying something like the following:
UPDATE foo
SET bar=bar-1
WHERE baz=
(
SELECT baz
FROM foo
WHERE fooID='1'
)
其中foo 是主键fooID 的表名.bar 和 baz 是 INT 类型.执行此操作时出现以下错误:
Where foo is the table name with primary key fooID. bar and baz are of type INT. When executing this I get the following error:
Error: A query failed. You can't specify target table 'foo' for update
in FROM clause
推荐答案
从此网络文章
这个错误的原因是当你在内部选择中使用同一个表作为更新条件时，MySQL 不允许更新表.文章接着提供了一个解决方案，即使用临时表.
The reason for this error is that MySQL doesn’t allow updates to a table when you are also using that same table in an inner select as your update criteria. The article goes on to provide a solution, which is to use a temporary table.
使用这个例子，你的更新应该是这样的:
Using this example, your update should be this:
update foo
set bar = bar - 1
where baz in
(
select baz from
(
select baz
from foo
where fooID = '1'
) as arbitraryTableName
)
这篇关于MYSQL 更新与 WHERE SELECT 子查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！