我一直在MySQL Workbench和SSMS之间工作时无法理解两者的语法
我遇到的最大问题是更新语句。
我已经编写了一个查询来显示我希望在更新之前更改的项目,它运行良好,但是当我尝试将 UPDATE 与它配对时,它不想工作
将要包含的更新语句是
UPDATE Book
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';
首先,您可以简化代码:
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. Instead, use
连接的set
或where
部分中引用正在更新的表:
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 . . .;