google "mysql update syntax" and "mysql select syntax" and you have the workings of both queries explained. Once you understand how each separate part works, it's just a matter of using logic to break things down
outer query (UPDATE)
WHERE NOT EXISTS (
nested query (SELECT)
)
LIMIT 1;
Which in short means that every row in table aliased as r1 should get updated where either one or both of these conditions are not met
- the ids of the same table aliased as r2 doesn't match, (to avoid comparing a team with itself)
- team in r2 has more points than team in r1
... which leads me to believe there is an error in logic, since there is no match when team r1 is compared against itself in r2:
r1.team != r2.team => false
false AND 'whatever' => false
WHERE NOT EXISTS (false) => true
... and every team gets +50 credits at least once!
It should instead be
WHERE exists (... WHERE r1.team != r2.team AND r2.score < r1.score)
since there exists a match only when not comparing the team to itself and the r1 team (being updated) has a higher score.
However, I'm guessing you are missing another factor in the table structure as well. If you have 3 teams and everyone plays the other teams once, you will end up with 3 games (2+1), for 4 teams it's 6 games (3+2+1) etc. And since there are two teams in each game, there will be two entires per game, giving 6 game entries with 3 teams, 12 entries with 4 teams etc
But, before I continue, your SQL queries tells me your database schema is called database and your table name is called row, which is rather noninformative and seriously confusing. I'm not going to specify database at all, since we both know what database we're talking about, but I am going to call the table with game results (i.e. columns team + score) "game".
So, let's assume 3 teams, #1, #2 and #3, with these data in "game"
id team score
-------------------
-- let's, for now, assume, the first two entires are #1 vs #2
1 #1 3
2 #2 5
-- let's, for now, assume, the next two entires are #2 vs #3
3 #2 0
4 #3 2
-- let's, for now, assume, the last two entires are #1 vs #3
5 #1 5
6 #3 4
This means that when you get to the subquery for "WHERE EXISTS", which according to my reasoning above should be
SELECT 1
FROM game
WHERE r1.team != r2.team AND r2.score < r1.score
it's evident that for this query to have any existing rows in the result set, r1.team != r2.team and r2.score < r1.score must hold. Thus, we know that r1.team != r2.team must be true, which in turn gives us 2 games that potentially are true where r1.team didn't even participate, namely when #2 played #3.
And, in this case, when inspecting the first entry, both rows 3 and 4 (team #2 vs #3) have lower score than #1 had in the first game, and #1 is giving credit twice for winning in a game they didn't take part in (and incidentally, the same happens for team #1's second game where they also had a higher score)
As such, to deal with several teams, you will need to specify the opposing team, which yields (for the same games as above)
[code]
id team score vs
-----------------------
-- let's, for now, assume, the first two entires are #1 vs #2
1 #1 3 2
2 #2 5 1
-- let's, for now, assume, the next two entires are #2 vs #3
3 #2 0 3
4 #3 2 2
-- let's, for now, assume, the last two entires are #1 vs #3
5 #1 5 3
6 #3 4 1
and you obviously need to modify your select query to
-- specify that you only consider games where r1.team was the opponent
WHERE r1.team != r2.team AND r2.score < r1.score AND r1.team = r2.vs
-- and since r1.team is never an opponent vs itself, this can be simplified to
WHERE r1.team = r2.vs AND r2.score < r1.score
Which, upon inspection gives that there are only two potential rows that will give credit to team 1 as winner, rows 2 and 6, which are indeed the two rows to inspect.
However, should this table contain data from more than one game for the same two teams, which it will if every team plays the other team twice, you will once again be considering games that has no relation to the score from the game you are updating, and you'd need to also add a game id
id team score vs game
-----------------------------
-- game 1, #1 vs #2
1 #1 3 2 1
2 #2 5 1 1
-- game 2, #2 vs #3
3 #2 0 3 2
4 #3 2 2 2
-- game 3, #1 vs #3
5 #1 5 3 3
6 #3 4 1 3
-- game 4, #1 vs #3 (second game between these teams)
1 #1 3 2 1
2 #2 5 1 1
-- game 5, #2 vs #3 (second game between these teams)
3 #2 0 3 2
4 #3 2 2 2
-- game 6, #1 vs #2 (second game between these teams)
5 #1 5 3 3
6 #3 4 1 3
and so...
WHERE r1.game = r2.game AND r1.team = r2.vs AND r2.score < r1.score
-- but suddenly we no longer need the "vs" column, since we are always inspecting
-- ONLY the same game as we look at in r1, which means that if r1.team != r2.team
-- then it's the other of the two possible rows that we are looking at.
WHERE r1.game = r2.game AND r1.team != r2.team AND r2.score < r1.score
And finally, when one team takes part in multiple games, it becomes evident that you cannot also store each team's credits in this table. Credits has no relation to one single game, it is related to several games. If these credits are somehow "spendable", and #1 spends 10 credits, how many rows should you update for this to take effect? And what if you miss updating one of #1's several games, then #1 may have 50 credits in one row and 40 in another...
Thus, a new table is needed
-- table team
id name score
1 #1 0
2 #2 0
3 #3 0
And now, you no longer need to store the team name several times for each team in the game table. You will instead use the team id. Which with my example data means that for game.team, instead of storing #1 (name - string) you instead store 1 (team.id - int), and the update query instead becomes
UPDATE team
INNER JOIN
game g1 ON g1.team = team.id
SET credits = credits + 50
WHERE EXISTS (
SELECT 1
FROM game g2
WHERE g2.team != g1.team AND
g2.points < g1.points AND
g1.game = g2.game
)
LIMIT 1;
And as usual when I write SQL update queries, make bloody certain you actually test my code on non-live data (or transaction-rollbackable) to see that it really does what it should.
