Advertisement: Jobs in UAE, Saudi Arabia, Qatar, Pakistan, Bahrain & other regions. Click here to find one for you.

MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
Powered by MySQL
 
Home / Errors / Question No: 156

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?

Answer No: 156

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;

Related MySQL FAQs to the Above FAQ

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

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

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

1264-Out-of-range-value-adjusted-for-column 1264 Out of range value adjusted for column

1048-Column-cannot-be-null 1048 Column cannot be null

1191-Cant-find-FULLTEXT-index-matching-the-column-list 1191-Cant find FULLTEXT index matching the column list

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Sulata iSoft - Limitless Solutions | Limitless Job Opportunities - Careermidway.com

© 2007 - 2014  www.mysqlfaqs.net
All rights reserved. The site is owned and operated by Mr. Javed Bhatti - Pakistan.