How can I use a trigger to update another table in MySQL?

Question

I have two MySQL tables. Voting table (id, userId, postId, voteTypeId)and column table (id, postTypeId, userId, parentId). I am writing a trigger that fires after inserting voices.

I want the trigger to update the message in the message table. But this post is not the one indicated in the table of my votes in the section postId; He is the parent of this message.

BEGIN
CASE NEW.voteTypeId
    WHEN 2 THEN UPDATE posts SET posts.acceptedAnswerId = NEW.postId WHERE posts.id = @the parent postId of NEW.postId
    ELSE
        BEGIN
        END;
    END CASE;
END

I tried using this instead of @ parent ...:

(SELECT posts.parentId FROM posts WHERE posts.id = NEW.postId)

But you, I don’t think you can do SELECTS in triggers unless you use some type of SELECT INTO syntax. My only link to the parent mail that I want to update is its child post in the links in the voices. Therefore, I do not know how to do the update without capturing the correct identifier using the selection.

?

Answer 1

:

BEGIN
  IF (NEW.voteTypeId = 2) THEN
    UPDATE
      posts AS p
    CROSS JOIN
      posts AS p2
    ON
      p.id = p2.parentId
    SET
     p.acceptedAnswerId = NEW.postId
    WHERE
     p2.id = NEW.postId;
  END IF;
END