Menu
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 
456
In MySQL subquery can be nested inside a SELECT, INSERT, UPDATE, DELETE, SET, or DO statement or inside another subquery. A subquery is usually added within the WHERE Clause of another SQL SELECT statement.
The subquery either returns a single row, or else has no correlated column references. The subquery is in the UPDATE statement WHERE clause, using Condition with Subquery syntax. No SPL routine in the subquery can reference the same table that UPDATE is modifying.
Use an INNER JOIN to join with your max ID's. Assuming the ID column is indexed, this is likely as fast as its going to get. MySQL will create many temporary tables, while in my example there will be only one.
MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.
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 ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
