MySQL update statement MySQL Workbench

Question

I've been having trouble working between MySQL Workbench and SSMS to understand the syntax of both

the biggest problem i have run into is with an update statement.

I have written a query to display the items i wished to change prior to the update which works perfectly but when i try to pair an UPDATE with it, it doesnt want to work

the update statement that was going to be included was

UPDATE Book SET Shelf_Location = 'KD-2222'

the query in question is

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
        )
 );

From the resources ive been looking at it mentions using Select * FROM () and putting the subquery in the parenthesis of the from.

Any help is appreciated.

Thanks

EDIT:

I found the missing issue with the help of Gordon Linoff and some experimentation

the corrected code is:

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

First, you can simplify the code:

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 doesn't allow you to reference the table being updated in the set or where part of the update. Instead, usejoin`:

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 . . .;