突然發現，MS SQL Server 的 sub query 滿好用的，在 mysql 這邊，那個每一row 需要另外做的loop 需要自己來用temp table 來 handle.
all you should do is just join the tables like this.
UPDATE table2 t2 JOIN table1 t1 ON t1.id = t2.id SET t2.name = t1.name;
if you are set on doing it with a select you could do it like this.
UPDATE table2 t2, ( SELECT Name, id FROM table1 ) t1 SET t2.name = t1.name WHERE t1.id = t2.id
The main issue is that the inner query cannot be related to your
where clause on the outer
updatestatement, because the where filter applies first to the table being updated before the inner subquery even executes. The typical way to handle a situation like this is a multi-table update.
Update Competition as C inner join ( select CompetitionId, count(*) as NumberOfTeams from PicksPoints as p where UserCompetitionID is not NULL group by CompetitionID ) as A on C.CompetitionID = A.CompetitionID set C.NumberOfTeams = A.NumberOfTeams