sql - 如何将这个 SQL 更新功能合二为一？

Question

我想根据来自不同表的团队名称设置两个不同的分数。怎么做？

UPDATE match_detail
SET team_score = 2
WHERE match_id LIKE 'MT010' AND 
team_id IN (SELECT team.team_id
    FROM team, match_detail
    WHERE team.team_id = match_detail.team_id
    AND team_name LIKE 'Invictus Gaming');

UPDATE match_detail
SET team_score = 0
WHERE match_id LIKE 'MT010' AND 
team_id IN (SELECT team.team_id
    FROM team, match_detail
    WHERE team.team_id = match_detail.team_id
    AND team_name LIKE 'Vici Gaming');

标签： sqlsql-server

Answer 1

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