SQL Update Query

[sila]

Hi
Can anyone tell me the sql query I need to set a field called 'Winner' to equal 1 in the record that has the highest number of 'VotesReceived' (there may be three or four records)these records are identified by a field called DivElecID.

Thanks!
Sila

 

[dbomrrsm]

update yourtable
set Winner = 1
where votesreceived =
(Select max(votesreceived) from yourtable)

best try a select with the same where first to see you get the correct records.

[blue]DBomrrsm[/blue]

 

[sila]

Thanks Dbomrrsm
Where do I place my other clause of WHERE DivElecID = @DivElecID in this?

Cheers

 

[dbomrrsm]

update yourtable
set Winner = 1
where votesreceived =
(Select max(votesreceived) from yourtable)
AND DivElecID = @DivElecID

[blue]DBomrrsm[/blue]

 

[sila]

I'm entering this:

update dbo.Candidate
set Winner = 1
where VotesReceived =
(Select max(VotesReceived) from dbo.Candidate)
AND DivElecID = @DivElecID

and I'm getting this message...
(I am setting the parameter information first)

Parameter information cannot be derived from sql statements with sub- select queries. Set parameter information before preparing command.

Any ideas? - Thanks again.

 

[dbomrrsm]

declare @DivElecID int
set @DivElecID = 10
update dbo.Candidate
set Winner = 1
where VotesReceived =
(Select max(VotesReceived) from dbo.Candidate)
AND DivElecID = @DivElecID

[blue]DBomrrsm[/blue]

 

[sila]

Thanks for your help but it doesnt seem to work.

 

[donutman]

[Blue]DECLARE[/Blue] @DivElecID [Blue]int[/Blue]
[Blue]DECLARE[/Blue] @MaxVotes [Blue]int[/Blue]
[Blue]SET[/Blue] @DivElecID [Gray]=[/Gray] 10
[Blue]SET[/Blue] @MaxVotes[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]VotesReceived[Gray])[/Gray] 
                  [Blue]FROM[/Blue] dbo.Candidate[Gray])[/Gray]
[Blue]UPDATE[/Blue] dbo.Candidate
   [Blue]SET[/Blue] Winner [Gray]=[/Gray] 1
   [Blue]WHERE[/Blue] VotesReceived [Gray]=[/Gray] @MaxVotes [Gray]AND[/Gray] 
         DivElecID [Gray]=[/Gray] @DivElecID

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]