When I try to run update query for my table "comments", MySQL returns the #1093 - You can't specify target table 'comments' for update in FROM clause message. My contrived table structure and update query are as follow:
CREATE TABLE comments(id int primary key, phrase text, uid int);
INSERT INTO comments VALUES(1, 'admin user comments',1),
(2, 'HR User Comments',2),
(3, 'RH User Comments',2);
SET phrase = (SELECT phrase FROM comments WHERE uid=2 AND id=2)
WHERE id = 3;
Is there any easy way to work around the #1093 - You can't specify target table 'comments' for update in FROM clause error?
Actually, your above update query seems illegal as per SQL standard. MySQL does not allow to UPDATE or DELETE a table's data if you're simultaneously reading that same data with a subquery. Because you are doing so that is why MySQL tersely said its such error message. Therefore, you will have to rewrite your above update query.
Since MySQL materializes sub queries in the FROM Clause as temporary tables, wrapping the subquery into another inner subquery in the FROM Clause causes it to be executed and stored into a temporary table, then referenced implicitly in the outer subquery. So, the update query will succeed by rewriting it like below:
SET phrase =( SELECT phrase FROM
SELECT * FROM comments
WHERE c1.uid=2 AND c1.id=2
) WHERE id =3;