sDbJCf3N

1alter table books add bestseller boolean;
2
3drop procedure if exists UpdateBestsellers;
4DELIMITER $$
5
6create procedure UpdateBestsellers()
7begin
8	declare LBOOK_ID int;
9    declare FINISHED int default 0;
10    declare ALL_BESTSELLERS cursor for select book_id from rents t 
11							group by  EXTRACT( YEAR_MONTH FROM rent_date),book_id having count(*) > 2 ;
12    declare continue handler for not found set FINISHED =1;
13    OPEN ALL_BESTSELLERS;
14    while(FINISHED = 0) DO
15		FETCH ALL_BESTSELLERS INTO LBOOK_ID;
16        IF(FINISHED = 0) then
17			update books set bestseller = true 
18				where BOOK_ID = LBOOK_ID ;
19			commit;
20		end IF;
21	END WHILE;
22    CLOSE ALL_BESTSELLERS;
23END $$
24delimiter ;
25
26select * from books;
27call UpdateBestsellers;