Reputation:
I'm receving the following error when executing the code below and not sure why I'm getting it because I'm using a SELECT TOP. I cannot see any output when I execute the query but can anyone see what's wrong with it?
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
;WITH cte AS
(
SELECT f.FixtureID,
ht.FinalTeamWeight - at.FinalTeamWeight AS TeamScore
FROM dbo.Fixture f
...
)
UPDATE f
SET f.HomeScore = s.HomeScore,
f.AwayScore = s.AwayScore
FROM dbo.Fixture f
INNER JOIN
(
SELECT FixtureID,
TeamScore,
(
SELECT
CASE
WHEN c.TeamScore BETWEEN HomeWeighting AND AwayWeighting AND HomeScore > AwayScore AND s.ScoreDifference = sr.ScoreDifference
THEN (SELECT TOP 1 ScoreID FROM dbo.Score WHERE HomeScore > AwayScore AND ScoreDifference = 1 ORDER BY NEWID())
END AS ScoreID
FROM dbo.Score s
LEFT JOIN ScoreReference sr
ON s.ScoreDifference = sr.ScoreDifference
) AS ScoreID -- end select case
FROM cte c
) -- end inner join
AS ScoreResult
ON f.FixtureID = ScoreResult.FixtureID
INNER JOIN Score s
ON ScoreResult.ScoreID = s.ScoreID
INNER JOIN ScoreReference sr
ON s.ScoreDifference = sr.ScoreDifference
TABLES: SCORE_REFERENCE
SCORE:
Upvotes: 0
Views: 421
Reputation: 474
It's probably not the onw with TOP but the one which contains it. Add a TOP 1 on that one also
Upvotes: 0
Reputation: 2104
Just add TOP(1) here...
SELECT TOP(1)
CASE
WHEN c.TeamScore BETWEEN HomeWeighting AND AwayWeighting AND HomeScore > AwayScore AND s.ScoreDifference = sr.ScoreDifference
THEN (SELECT TOP 1 ScoreID FROM dbo.Score WHERE HomeScore > AwayScore AND ScoreDifference = 1 ORDER BY NEWID())
END AS ScoreID
FROM dbo.Score s
LEFT JOIN ScoreReference sr
ON s.ScoreDifference = sr.ScoreDifference
Upvotes: 1