mysql update trigger: update "some" other rows -

table "book":

bookid | title 1      | java 2      | mysql 3      | zen 

table "bestpage":

pageid | bookid | isbestpage 1      | 1      | 0  2      | 1      | 0  3      | 1      | 1  4      | 2      | 0  5      | 2      | 1  6      | 2      | 0 

so books may have "bestpage", only one. need beforeupdate trigger when update row pageid 2, , set isbestpage = 1, other pages book have isbestpage set 0.

something like:

for each row begin if old.isbestpage = 1 ,  -- here want tell **only current book** ! set new.isbestpage = 0 

or that.
is, if mark page bestpage, other pages within book should set not best page.
thanks.

unfortunately can't in mysql trigger

according e.1. restrictions on stored programs
within stored function or trigger, not permitted modify table being used (for reading or writing) statement invoked function or trigger.

what can though alleviate pain create stored procedure this

delimiter $$ create procedure sp_update_bestpage(in pid int) begin     update bestpage p join            bestpage p1 on p.bookid = p1.bookid        set p.isbestpage = 0       p1.pageid = pid , p.pageid <> pid;     update bestpage        set isbestpage = 1       pageid = pid; end $$ delimiter ; 

and use it

call sp_update_bestpage(2); 

sqlfiddle