Wrong SQLServer syntax

this is what I want to achieve:

4 tables are involved:

• Players with PlayerID as PK,
• Competitions with CompetID as PK
• Results with ResultID as PK and CompetID as FK

And the 4th table: PlayerResultts with ResultID + PlayerID as PK and CompetID as new column I created.

Competitions, results and PlayerResults are already populated and quite large (300000 PlayerResults so far).

In order to populate the PlayerResults.CompetID column, I try a Update ... (Select....)开发者_JS百科 request but I'm not aware of the right syntax and it fails.

Here is my feeble attempt:

update PlayerResults
set competid = (select distinct(r.competid) from results r, playerresults p  
where r.resultID = p.resultid)

Error is (of course):

"Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Can someone put me in the right direction? TIA

---

You don't need distinct

update PlayerResults
set competid = r.competid
from results r
where r.resultID = PlayerResults.resultid