CODEWITHSUNDEEP
sqlsql-server
henry
henry

Reputation: 47

How to combine this SQL update function into one?

I want to set two different score base on the name of a team from different table. How to do it?

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');

Upvotes: 0

Views: 44

Answers (1)

M.Ali
M.Ali

Reputation: 69514

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')

Upvotes: 3

Related Questions