CODEWITHSUNDEEP
sqlsql-serversyntaxsql-update
Didier Levy
Didier Levy

Reputation: 3453

Wrong SQLServer syntax

this is what I want to achieve:

4 tables are involved:

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

Upvotes: 0

Views: 71

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

You don't need distinct

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

Upvotes: 2

Related Questions