在触发器中使用连接，MySQL [英] using a join inside a trigger, MySQL

问题描述

我正在开发一个数据库，当另一个表更新时，它需要更新一个表中的字段.但是，正在更新的表不包含我需要的值……它只包含具有该值的第三个表的 ID.基本上，我正在寻找可以做到这一点的东西:

I'm developing a database that needs to update a field in one table when another table is updated. However, the table that is being updated doesn't contain the values I need... it just contains an ID to a third table that has that value. Basically, I'm looking for something that does this:

CREATE TRIGGER au_TableA AFTER UPDATE TableA
FOR EACH ROW BEGIN
UPDATE TableB SET points=TableC.points 
WHERE TableC.cID=NEW.cID;
END

cID，当然，同时出现在 TableA 和 TableC 中.

cID, of course, being present in both TableA and TableC.

推荐答案

试试这个查询 -

UPDATE
  TableB b
  JOIN TableC с
    ON c.cID = b.cID
SET
  b.points = c.points
WHERE c.cID=NEW.cID;

这篇关于在触发器中使用连接，MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！