带有 WHERE SELECT 子查询错误的 MYSQL 更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6944165/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MYSQL update with WHERE SELECT subquery error
提问by Erik
I have an issue with getting select sub-queries to work on an UPDATE. I'm trying something like the following:
我在获取选择子查询以处理UPDATE. 我正在尝试类似以下内容：
UPDATE foo
SET bar=bar-1
WHERE baz=
(
SELECT baz
FROM foo
WHERE fooID='1'
)
Where foois the table name with primary key fooID. barand bazare of type INT. When executing this I get the following error:
foo带有主键的表名在哪里fooID。bar并且baz是 INT 类型。执行此操作时，我收到以下错误：
Error: A query failed. You can't specify target table 'foo' for update
in FROM clause
回答by DwB
From this web article
来自这篇网络文章
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.
此错误的原因是当您还在内部选择中使用同一表作为更新条件时，MySQL 不允许更新表。文章继续提供了一种解决方案，即使用临时表。
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
)
回答by ace
Because of error 1093 Error 1093 (ER_UPDATE_TABLE_USED) SQLSTATE = HY000. The work around is to create a temporary table.
由于错误 1093 错误 1093 (ER_UPDATE_TABLE_USED) SQLSTATE = HY000。解决方法是创建一个临时表。
CREATE TEMPORARY table foo_bak (SELECT baz from foo WHERE fooID='1');
UPDATE foo
SET foo.bar=foo.bar-1
WHERE foo.baz =
(
SELECT baz
FROM foo_bak
);
DROP TABLE foo_bak;
回答by P. R. Ribeiro
So far as I know, when updating a table, Mysql locks it in order to do a safe update. You cannot select data and update the same table as you're trying.
据我所知，更新表时，Mysql 会锁定它以进行安全更新。您无法在尝试时选择数据并更新同一个表。
Those texts should help you
这些文字应该对你有帮助
回答by Lukas Jelinek
In some cases you can also take advantage of the MySQL variable. e.g.:
在某些情况下，您还可以利用 MySQL 变量。例如：
SET @id1 = (SELECT id FROM foo WHERE name = 'parent');
UPDATE foo SET parent_id = @id1 WHERE name = 'emails';
