-1

Good evening!

I have a trigger to update a record from my table 'comment' named vote. The trigger will be fired when I update a value named 'vote' in my 'vote' table. For the trigger query, I need to sum up all 'vote' records in table 'vote' and then update in 'comment' the row with the 'comment.id = vote.pid' while 'vote.uid = user.id'.

Trigger Function:

DROP TRIGGER IF EXISTS `CommentVoteOnUpdate`;CREATE DEFINER=`root`@`localhost` TRIGGER `CommentVoteOnUpdate` AFTER UPDATE ON `vote` FOR EACH ROW UPDATE comment
SET comment.vote = (
    SELECT SUM(vote.vote) 
    FROM vote, comment, user
    WHERE comment.id = pid AND uid = user.id
)
WHERE comment.id = new.pid

When I want to update a value in the 'vote' table , then I got the following error message:

"Table 'comment' is specified twice, both as a target for 'UPDATE' and as a separate source for data"

Did anyone of you got a solution for my problem?

Hans Martin
  • 337
  • 1
  • 13

1 Answers1

0

You need to give your comment table (In the subquery) an alias. See this question for more info.

Community
  • 1
  • 1
Blue
  • 22,608
  • 7
  • 62
  • 92