如何将两条SQL UPDATE更新操作合并为单条执行语句？

合并更新语句的实现方案 #

你可以用CASE分支表达式配合多表关联更新，把两条语句合并为一条，相比原写法执行效率更高，也避免重复编写过滤逻辑：

UPDATE match_detail md
INNER JOIN team t ON md.team_id = t.team_id
SET md.team_score = CASE 
    WHEN t.team_name = 'Invictus Gaming' THEN 2
    WHEN t.team_name = 'Vici Gaming' THEN 0
END
WHERE md.match_id = 'MT010'
AND t.team_name IN ('Invictus Gaming', 'Vici Gaming');

说明 #

• 你原代码中使用LIKE但没有加通配符，等价于等值匹配=，直接替换为=可以减少不必要的模糊匹配开销
• 如果你需要保留这两个队伍之外的原有分值，只需在CASE分支最后加ELSE md.team_score即可
• 如果你的数据库不支持MySQL风格的关联更新，可以改用子查询版本的写法：

UPDATE match_detail
SET team_score = CASE
    WHEN team_id IN (SELECT team_id FROM team WHERE team_name = 'Invictus Gaming') THEN 2
    WHEN team_id IN (SELECT team_id FROM team WHERE team_name = 'Vici Gaming') THEN 0
END
WHERE match_id = 'MT010'
AND team_id IN (
    SELECT team_id FROM team 
    WHERE team_name IN ('Invictus Gaming', 'Vici Gaming')
);

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