突然發現,MS SQL Server 的 sub query 滿好用的,在 mysql 這邊,那個每一row 需要另外做的loop 需要自己來用temp table 來 handle.
sample1:
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
sample 2:
The main issue is that the inner query cannot be related to your where
clause on the outer update
statement, 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
Demo: http://www.sqlfiddle.com/#!2/a74f3/1