mySQL foreach row run an update

• Progress table has progressId, lessonId and uniqueId
• TestResults table has progressId
• TestResults table now has lessonId and uniqueId

This SQL statement foreach progrssId in TestResults will get lessonId and uniqueId from Progress:

select progressId,
       (select lessonId from Progress p where p.progressId = TestResults.progressId) as lessonId,
       (select uniqueId from Progress p where p.progressId = TestResults.progressId) as uniqueId
from TestResults
where progressId is not null;

Now I want to update lessonId and uniqueId in TestResults table foreach associated progressId. I need foreach idea. I don’t know how to do that in SQL.

Answer

You need to join the 2 tables in the UPDATE statement:

update TestResults t
inner join Progress p on p.progressId = t.progressId
set t.lessonId = p.lessonId,
    t.uniqueId = p.uniqueId