mysql - 使用子查询更新多行的多个字段

Question

是否可以使用子查询更新多行的多个字段？

cdu_user_progress:

------------------------------------------
|id |lesson_id |game_id |score |duration |
------------------------------------------
|1  |1         |0       |50    |2500     |
|1  |1         |1       |75    |2500     |
|1  |2         |0       |0     |10000    |
|1  |3         |0       |25    |1000     |
|1  |3         |1       |25    |40000    |
|1  |3         |2       |90    |3000     |
|1  |4         |0       |50    |5000     |
------------------------------------------

cdu_user_progress2

------------------------------------------
|id |lesson_id |game_id |score |duration |
------------------------------------------
|1  |1         |0       |0     |0        |
|1  |1         |1       |0     |0        |
|1  |2         |0       |0     |0        |
|1  |3         |0       |0     |0        |
|1  |3         |1       |0     |0        |
|1  |3         |2       |0     |0        |
|1  |4         |0       |0     |0        |
------------------------------------------

也许是这样的？

UPDATE cdu_user_progress2
SET
    score = sq.score,
    duration = sq.duration
FROM (
    SELECT 
        up.lesson_id AS lesson_id, 
        up.game_id AS game_id, 
        up.score AS score,
        up.duration AS duration
    FROM 
        cdu_user_progress up
) sq
WHERE 
    lesson_id = sq.lesson_id AND
    game_id = sq.game_id

出于本文的目的，我简化了表格和查询，但原理是相同的......

感谢任何帮助!

Answer 1

只需连接两个表:

UPDATE cdu_user_progress2 AS up2
JOIN cdu_user_progress AS up ON up2.lesson_id = up.lesson_id AND up2.game_id = up.game_id
SET up.score = up2.score, up.duration = up2.duration