MYSQL 更新与 WHERE SELECT 子查询错误

Question

我在获取选择子查询以处理 UPDATE 时遇到问题。我正在尝试以下方法:

UPDATE foo
   SET bar=bar-1
 WHERE baz=
      (
       SELECT baz
       FROM foo
       WHERE fooID='1'
      )

其中foo 是主键fooID 的表名。 bar 和 baz 是 INT 类型。执行此操作时出现以下错误:

Error: A query failed. You can't specify target table 'foo' for update 
in FROM clause

Answer 1

从此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.

使用这个例子，你的更新应该是这样的:

update foo
set bar = bar - 1
where baz in
(
  select baz from
  (
    select baz
    from foo
    where fooID = '1'
  ) as arbitraryTableName
)