Home >Database >Mysql Tutorial >You can't specify target table for update in FR

You can't specify target table for update in FR

WBOY
WBOYOriginal
2016-06-01 09:58:201319browse

今天执行一个mysql语句的时候,出现错误提示:You can't specify target table for update in FROM clause
mysql 语句如下;

<code>UPDATE school_more_info SET comments = replace( comments, '�', "'" ) WHERE school_id IN (SELECT school_id FROM school_more_info WHERE comments LIKE '%�%')</code>


这个sql的意思是将comments字段中的�替换更新成‘, 更新的条件是只有含有�的行才更新,没有�的行则不更新。咋一看,应该没有错啊,我们经常这么写。最后查资料,mysql中不能这么用,那串英文错误提示就是说,不能先select出同一表中的某些值,再update这个表(在同一语句中)。

改写后的sql,这样是可以正确执行的。如下:

<code>UPDATE school_more_info SET comments = replace( comments, '�', "'" ) WHERE school_id IN (SELECT school_id FROM (SELECT *FROM school_more_info WHERE comments LIKE '%�%') AS a)</code>

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn