使用WHERE SELECT子查询错误进行MYSQL更新 [英] MYSQL update with WHERE SELECT subquery error

问题描述

我在选择子查询以在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
)

这篇关于使用WHERE SELECT子查询错误进行MYSQL更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！