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

Question

I have two MySQL tables. A votes table (id, userId, postId, voteTypeId), and a posts table (id, postTypeId, userId, parentId) table. I'm writing a trigger that fires after insert on votes.

I would like the trigger to update a post in the posts table. But this post is not the same one referenced in my votes table under postId; It is the parent of that post.

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 @the parent of... :

(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 reference to the parent post that I want to update is its child postId in referenced in votes. So I don't know how to do the update without grabbing the right id through a select.

Is this possible?

Answer 1

I'd do it like that:

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