By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,024 Members | 2,027 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,024 IT Pros & Developers. It's quick & easy.

MYSQL trigger You can't specify target table 'XXX' for update in FROM clause

P: 22
I am trying to convert mssql triggers to mysql. I am very new to Mysql and would like some help with my code. My queries work but there has to be a more efficient way to write them. I would greatly appreciate any help. the following is a trigger that works until I add the last update statement. thanks for the help.
Mysql 5

Expand|Select|Wrap|Line Numbers
  1. delimiter //
  2. Create TRIGGER User_Rating_Avg After Insert on bksite.Ratings
  3. FOR EACH ROW
  4. BEGIN
  5. UPDATE bksite.bookkeepers SET User_Cost_Avg=
  6. (SELECT SUM(cost)/(SELECT count(*) FROM bksite.ratings where bkid = new.bkid) from ratings where bkid = new.bkid)
  7. where bkid = new.bkid;
  8.  
  9. UPDATE bksite.bookkeepers SET User_Knowledge_Avg=
  10. (SELECT SUM(knowledge)/(SELECT count(*) FROM bksite.ratings where bkid = new.bkid) from ratings where bkid = new.bkid)
  11. where bkid = new.bkid;
  12.  
  13. UPDATE bksite.bookkeepers SET User_Speed_Avg=
  14. (SELECT SUM(speed)/(SELECT count(*) FROM bksite.ratings where bkid = new.bkid) from ratings where bkid = new.bkid)
  15. where bkid = new.bkid;
  16.  
  17. UPDATE bksite.bookkeepers SET User_Accuracy_Avg=
  18. (SELECT SUM(accuracy)/(SELECT count(*) FROM bksite.ratings where bkid = new.bkid) from ratings where bkid = new.bkid)
  19. where bkid = new.bkid;
  20. END;
  21. //
  22.  
the trigger fails when i insert this last bit. the error is You can't specify target table 'XXX' for update in FROM clause

Expand|Select|Wrap|Line Numbers
  1. UPDATE bksite.bookkeepers SET User_Avg_Total=
  2. (SELECT (User_knowledge_avg + User_Cost_Avg + User_Speed_Avg + User_Accuracy_Avg)/4 FROM Bookkeepers
  3. where bkid =new.bkid);
  4.  
Nov 3 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.