MySQL error in select statement inside of trigger

Question

I've recently started working on MySQL n right now i want to create a trigger but MySQL is returning an error in my syntax.

delimiter $$;
create trigger abc after insert on ratings
for each row
    begin
        set @n1 = select avg(rating) from ratings join users where ratings.uname=users.uname 
        and ratings.bookid=new.bookid users.`type`='admin' or users.`type`='critic';
        update books set avgcriticrating = @n1 where bookid=new.bookid;
end;

The select statement runs perfectly when fired on its own but gives an error when i use it inside of the trigger.

Here's the error that MySQL gives

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select avg(rating) from ratings join users where ratings.uname=users.uname an' at line 4

Both the books and ratings table contain a field called bookid.

please help

Alex Monthy · Accepted Answer · 2012-10-05 20:43:35Z

1

If you want a single value from a select statement, the statement has to be in (brackets).

set @n1 = (select avg(rating) from ratings join users where ratings.uname=users.uname 
    and ratings.bookid=new.bookid users.`type`='admin' or users.`type`='critic');

The next error will occur at new.bookid users - there might be an and or an or missing.

answered Oct 5, 2012 at 20:43

Alex Monthy

1,8871 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL error in select statement inside of trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related