代码之家 › 专栏 › 技术社区 › roozbeh S

mysql根据条件同时更新多个表的多条记录

sql mysql

-1

roozbeh S · 技术社区 · 5 年前

我有一个mysql表,用于记录双人比赛,如下所示:

- gameid
- id1 // id of player 1
- id2 // id of player 2
- score1 // score of player 1
- score2 // score of player 2
- state // state of the games is initially 0, then the score updates are made and in order to prevent further updates the state must be updated to 1

我需要检查记录,并根据分数更新另一个“用户”表。前任: 如果 得分1>得分2 我需要更新三件事:

1- the state of the game // from 0 to 1
2- in table "users" add 1 point to the column score for the user with userid = id1
2- in table "users" subtract 1 point from the column score for the user with userid = id2

到目前为止,我可以更新1和2,但我需要在一个命令中完成所有3个更新:

UPDATE dbo.games AS GA , dbo.users AS US 
SET GA.state = 1, US.score = US.score + 1
WHERE US.id = GA.id1 AND GA.state = 0 and GA.score1 > GA.score2

我可以分开 1. 和 1. 命令,它会工作得很好。但是,当运行该命令时,两个用户的分数都应该更新。有人能帮忙吗?

1 回复 | 直到 5 年前

GMB 5 年前

这应该可以做到:

update dbo.games as ga, dbo.users as us
set 
    ga.state = 1, 
    us1.score = us1.score + case 
        when 
            (ga.score1 > ga.score2 and us.id = ga1.id1)
            or (ga.score2 > ga.score1 and us.id = ga2.id2)
        then 1
        else -1
    end
where 
    ga.state = 0 
    and ga.score1 <> ga.score2
    and us.id in (ga.id1, ga.id2)

逻辑是选择两排在用户表中,然后执行条件逻辑以决定是添加还是删除点。

注意:您没有说明要如何处理并列竞争,因此此查询明确忽略了它们。

Siva Koteswara Rao 5 年前

UPDATE dbo.games AS GA , dbo.users AS US     
SET GA.state = 1, (CASE WHEN US.id =GA.id1 THEN US.score = US.score + 1
 ELSE WHEN US.id=id2 THEN US.score =US.score-1 END)  
WHERE  GA.state = 0 and GA.score1 > GA.score2