如何编写满足跨表关联条件的SQL UPDATE查询语句？

直接批量更新指定ID的记录 #

如果只是需要一次性把ID1和ID4的text字段设为指定值，用IN条件即可：

UPDATE employee 
SET text = 'ABCABC' 
WHERE id IN ('ID1', 'ID4');

关联Address表实现同步更新（匹配emp_num和state） #

如果要基于ID1的emp_num和state，批量更新所有符合条件的记录（包括ID1和ID4），可以通过关联Address表获取匹配条件：

UPDATE employee e
JOIN address a ON e.address_id = a.address_id
SET e.text = 'ABCABC'
WHERE a.emp_num = (
    SELECT emp_num 
    FROM address 
    WHERE address_id = (SELECT address_id FROM employee WHERE id = 'ID1')
)
AND a.state = (
    SELECT state 
    FROM address 
    WHERE address_id = (SELECT address_id FROM employee WHERE id = 'ID1')
);

这条语句会找到和ID1属于同一个emp_num且同state的所有员工记录，统一更新text字段。

触发器实现自动同步（长期方案） #

如果希望每次插入或更新Employee记录时，自动同步同emp_num同state的其他记录，推荐创建触发器：

DELIMITER //
CREATE TRIGGER sync_employee_text_trigger
AFTER UPDATE ON employee
FOR EACH ROW
BEGIN
    -- 获取当前更新记录对应的emp_num和state
    DECLARE target_emp_num VARCHAR(255);
    DECLARE target_state VARCHAR(255);
    
    SELECT emp_num, state INTO target_emp_num, target_state
    FROM address
    WHERE address_id = NEW.address_id;
    
    -- 同步更新符合条件的其他记录
    UPDATE employee e
    JOIN address a ON e.address_id = a.address_id
    SET e.text = NEW.text
    WHERE a.emp_num = target_emp_num
      AND a.state = target_state
      AND e.id != NEW.id;
END //
DELIMITER ;

之后只要执行你的初始更新语句：

UPDATE employee SET text='ABCABC' WHERE id='ID1';

触发器会自动把同emp_num同state的其他记录（比如ID4）的text字段同步更新为'ABCABC'。

内容的提问来源于stack exchange，提问作者Groot