MySQL 更新语句 MySQL Workbench

Question

我在 MySQL Workbench 和 SSMS 之间工作时遇到困难，无法理解两者的语法

我遇到的最大问题是更新语句。

我编写了一个查询来显示我希望在更新之前更改的项目，该查询完美地工作，但是当我尝试将 UPDATE 与它配对时，它不想工作

要包含的更新语句是

更新书籍
SET Shelf_Location = 'KD-2222'

有问题的查询是

Select * 
FROM Book b0
WHERE b0.Subject_Code IN
(
SELECT b1.Subject_Code FROM Book b1
GROUP BY b1.Subject_Code
HAVING COUNT(*) = 
       (
        SELECT MIN(C.BookCount2) from
            (
            SELECT b2.Subject_Code, COUNT(*) as BookCount2 FROM Book b2
            GROUP BY b2.Subject_Code
            ) 
            as C
        )
 );

从我一直在查看的资源中，它提到使用 Select * FROM () 并将子查询放在 from 的括号中。

感谢任何帮助。

谢谢

编辑:

在 Gordon Linoff 和一些实验的帮助下，我发现了缺失的问题

更正后的代码是:

UPDATE book b JOIN
   (SELECT b1.Subject_Code
    FROM book b1
    GROUP BY b1.Subject_Code
    HAVING COUNT(*) = (
                            SELECT MIN(C.BookCount2) from
                                (
                                SELECT b2.Subject_Code, COUNT(*) as BookCount2 FROM Book b2
                                GROUP BY b2.Subject_Code
                                ) as C
                                )
                                ) 
                                as bs
   ON bs.subject_code = b.subject_code
   SET Shelf_Location = 'KD-2222'; 

Answer 1

首先，你可以简化代码:

Select * 
FROM Book b
WHERE b.Subject_Code IN (SELECT b1.Subject_Code
                         FROM Book b1
                         GROUP BY b1.Subject_Code
                         HAVING COUNT(*) = (SELECT COUNT(*) as BookCount2
                                            FROM Book b2
                                            GROUP BY b2.Subject_Code
                                            ORDER BY COUNT(*) DESC
                                            LIMIT 1
                                           )
                        );

MySQL 不允许您在 update 的 set 或 where 部分引用正在更新的表。相反，使用join`:

UPDATE book b JOIN
       (SELECT b1.Subject_Code
        FROM Book b1
        GROUP BY b1.Subject_Code
        HAVING COUNT(*) = (SELECT COUNT(*) as BookCount2
                           FROM Book b2
                           GROUP BY b2.Subject_Code
                           ORDER BY COUNT(*) DESC
                           LIMIT 1
                          )
       ) bs
       ON bs.subject_code = b.subject_code
    SET . . .;