Update Query

[sila]

Hi
I need to add some code to the query below which also sets any records other than the winner who has the same DivElecID to 0. Any help greatly appreciated!

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

Cheers
Sila

 

[NoCoolHandle]

update dbo.Candidate
set Winner = 0
where DivElecId = @DivElecID and
VotesReceived <>
(Select max(a.VotesReceived) from dbo.Candidate a
where a.DivElecID = @DivElecID)

 

[maswien]

Assume max(a.VotesReceived) never be 0, try this:

update dbo.Candidate
set winner = a.VotesReceived/(Select max(a.VotesReceived) from dbo.Candidate a where a.DivElecID = @DivElecID)
where DivElecId = @DivElecID

Or max(a.VotesReceived) can be 0, everybody are winners!

update dbo.Candidate
set winner =
( case when
VotesReceived = (Select max(a.VotesReceived) from dbo.Candidate a where a.DivElecID = @DivElecID)
then 1
else 0
end )
where DivElecId = @DivElecID