Wrong SQLServer syntax: need help!
- by user512602
Hi, 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....) 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
