Clouds blog

Category: Mysql

  • #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;

    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;

    Thanks to the original source:
    http://www.mysqlfaqs.net/mysql-faqs/Errors/1093-You-can-not-specify-target-table-comments-for-update-in-FROM-clause

  • Mysql – Restore only one database from sqldump

    Restoring the whole mysql dump is easy. Either use phpMyAdmin(if the file is not too big) or do it from the console. I had a hard time finding it how to restore only one database from the sqldump of all databases.

    We have a sqldump file which contains multiple databases. If you want to restore only one of them you should use the –one-database command.

    Example:
    I have a sqldump.sql which contains databases named dbispconfig, forum, gallery, … . On the target machine first create the database with the same name and then run the folowing command:

    mysql -u$username$ -p$password$ --one-database $selected_database$ < $sqldump_file$

    If i wanted to select only the “forum” database from the sqldump.sql I would create an empty “forum” database and then run:

    mysql -uroot -pMyPass --one-database forum < sqldump.sql

    That’s it.