mysql update where select,MYSQL更新，其中WHERE SELECT子查询错误

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'

)

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

解决方案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

)