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:

UPDATE comments
   SET phrase =( SELECT phrase FROM
	         (
	            SELECT * FROM comments
	         ) 
                 AS c1 
                 WHERE c1.uid=2 AND c1.id=2
               ) WHERE id =3;

Posted in: MySQL

Related FAQ's

Marius Ion ANGEL HOT SOFT LLC (800) 316-7677