CODEWITHSUNDEEP
mysqlsql
Shaik
Shaik

Reputation: 930

Mysql not updating the avg function of same table different column by trigger

i am trying to update a specific column that is column_2 by taking the average of rows of column_1 values where column_3 = value

but it gives a error

My code

Error: Can't update table 'moving_average' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

delimiter $$

Create TRIGGER takeaverage AFTER insert ON moving_table 
FOR EACH ROW  
BEGIN        
update moving_table b, (select avg(price) avg_ from moving_table ) v
set
  b.moving_avg = v.avg_price;


END;

Upvotes: 0

Views: 65

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30839

You can't do that, as per the documentaion:

A stored function or trigger cannot modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

I would rather write a BEFORE INSERT trigger and update the :New.moving_avg with the average of existing rows and the new price.

Upvotes: 1

Related Questions