MySQL Database learning



Home / Errors / 1093 You can not specify target table comments for update in FROM clause
Share this job:


1093 You can not specify target table comments for update in FROM clause

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

UPDATE comments
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:

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




Errors related other questions

Why I see an error when I EXPLAIN for update query?

Because EXPLAIN is used only with SELECT statements in MySQL that is why you were getting #1064 error while running EXPLAIN with UPDATE statement. So, there is no way to EXPLAIN UPDATE query ...

1264 Out of range value adjusted for column

The error 1264 Out of range value adjusted for column mostly occurs when INSERT query makes try to insert an empty value into a NOT NULL field. So \'id\' is a NOT NULL field in your tabl ...

ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

Exactly. MySQL doesn\'t support dropping and creating functions and procedures or triggers in functions or triggers. ...

ERROR 1064 (42000): You have an error in your SQL syntax

Your above query contains reserved words in it those are causing error 1064 (42000). ORDER is a reserved word in MySQL and in standard SQL as well. If the column name in the table is ORDER, then put backquotes ...

1093 You can not specify target table comments for update in FROM clause

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

1191-Cant find FULLTEXT index matching the column list

By your query, it seems that you are attempting to search the desired data using FULLTEXT INDEX functionality on NON-INDEXED columns of your table namely (title, detail, answer) that is why you are getting ...

1048 Column cannot be null

The error #1048 mostly occurs when INSERT query makes try to insert NULL values into a NOT NULL field. I assume that \'min\' is a NOT NULL field in your table against which you are inserting NU ...