php - mysql 更新子查询不起作用

Question

当我添加子查询时，mysql更新语法似乎不起作用。更新语法对子查询有限制吗？

不起作用:

    update books set imagename ='name' 
    where book_ID='(select book_ID from books order by book_ID desc limit 1)';

作品:

    update books set imagename ='name' 
    where book_ID='101';

Answer 1

MySQL 对使用正在更新的表的子查询确实有限制。所以你的是不允许的:

update books
    set imagename ='name' 
    where book_ID = (select book_ID from books order by book_ID desc limit 1);

相反，您可以这样做:

update books
    set imagename ='name' 
    order by book_ID desc
    limit 1;

这是 documentation 中的解释:

In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

    DELETE FROM t WHERE ... (SELECT ... FROM t ...);
    UPDATE t ... WHERE col = (SELECT ... FROM t ...);
    {INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:

    UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...);
 Here the result from the subquery in the FROM clause is stored as a temporary table, so the relevant rows in t have already been

selected by the time the update to t takes place.