MySQL 使用来自同一个表的值更新表

Question

我有一个包含 4 列的表，主键是 id、gameId 和 flag 的组合。

id | gameId | score | flag
--------------------------
1  | 1      | 10    | 1
1  | 1      | 20    | 0
2  | 1      | 1     | 0
1  | 2      | 10    | 1
3  | 2      | 1     | 0

我需要更新表格以便:

• 所有 gameId 2 的分数都添加到 gameId 1 中，其中 id 和 flag 相同。(例如，通过将第 1 行和第 4 行的分数相加，第 1 行的分数为 20)

• 如果发生上述情况，则需要删除 gameId 2 行。

• 如果没有找到上述内容(例如，有一行 gameId 为 2 但 id 和 flag 与另一行不匹配)，则 gameId 可以更改为 1。

所以 SQL 完成后我的表应该是这样的:

id | gameId | score | flag
--------------------------
1  | 1      | 20    | 1
1  | 1      | 20    | 0
2  | 1      | 1     | 0
3  | 1      | 1     | 0

我怎样才能用 SQL 写这个？谢谢:)

Answer 1

认为这行得通:

尝试 1

UPDATE score_list AS t2
LEFT JOIN score_list AS t1 ON
  (t1.id = t2.id AND t1.flag = t2.flag AND t1.gameId = 1)
SET
  t1.score = t1.score + t2.score,
  t2.gameId = IF(t1.gameId IS NULL, 1, t2.gameId)
WHERE t2.gameId = 2;

DELETE FROM score_list WHERE gameId = 2;

尝试 2

# add scores to gameId = 1
UPDATE score_list AS t2
LEFT JOIN score_list AS t1 ON
  (t1.id = t2.id AND t1.flag = t2.flag AND t1.gameId = 1)
SET
  t1.score = t1.score + t2.score
WHERE t2.gameId = 2;

# try to move gameID 2 to gameId 1, using ignore to allow rows to fail, as gameId alredy exists
UPDATE IGNORE score_list SET gameId = 1 WHERE gameId = 2;

# delete all not updated rows from last query
DELETE FROM score_list WHERE gameId = 2;