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?

You can in fact do a SELECT within a trigger. Your other syntax however looks a bit off. — Mchl
– Mchl, Commented Feb 4, 2011 at 16:10
@Mchl, I tried a few combinations, but it didn't work. Can you point me to what I'm doing wrong? — Mohamad
– Mohamad, Commented Feb 4, 2011 at 16:35

Mchl · Accepted Answer · 2011-02-04 18:31:43Z

2

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

edited Feb 4, 2011 at 18:31

answered Feb 4, 2011 at 18:26

Mchl

62.4k9 gold badges121 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mohamad Over a year ago

thanks for that. Can I just ask you why you use a cross join? Why not just a normal join with an alias?

Mchl Over a year ago

In MySQL INNER JOIN and CROSS JOIN and JOIN are all equivalent. Actually, according to ANSI SQL specification, I should have used INNER JOIN here (because I used ON join condition instead of USING()). Functionally it makes no difference, but potentially omproves portability.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related