Wrong SQLServer syntax

Question

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

Again a comment about answer acceptation! I do accept USEFUL answers, no problem. I DO NOT accept anything just to please peopme, sorry... — Didier Levy
– Didier Levy, Commented Feb 4, 2011 at 1:17

RichardTheKiwi · Accepted Answer · 2011-02-03 23:23:45Z

2

You don't need distinct

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

answered Feb 3, 2011 at 23:23

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan J Over a year ago

@user512602 Note what your problem was: your original query defined no correlation between columns in the table being updated and columns in the sub-query, so you were effectively taking a whole set and trying to assign it to a column. Notice how cyberwiki relates the instance of the table being updated to results.

Didier Levy Over a year ago

Man, this is just GREAT! And so quick an answer... StackOverflow is the definite source for solutions.

Didier Levy Over a year ago

I don't find 25% a low rate! Rate level depends on finding valuable answers, and here - sometimes - I got answers in minutes, which is great. That being said, I'm really amazed that you geeks are that hungry for acceptance and votes! As if they were US Dollars or even better, Euros...

Collectives™ on Stack Overflow

Wrong SQLServer syntax

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related