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
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.
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
)