I have a table that shows player's ranks after a two-player game. Table looks like:

I'd like to create a fourth column whose values get updated after each row entry based on who is playing whom

So if player A is playing player c next, I'd like the values to show

I've tried the below trigger, which doesn't work, and also doesn't allow any additional entries to my tables

CREATE TRIGGER opp_rank_update BEFORE INSERT INTO stats

FOR EACH ROW

UPDATE rank

SET opponent_rank = SELECT (SELECT previous_rank FROM rank WHERE player = new.winner)

WHERE player = new.loser

This trigger is referencing another table that has raw data:

CodePudding user response：

You can try using the following query:

WITH cte AS(
    SELECT *, 0 `Rank` - `Previous_rank`   AS diff
    FROM tab
)
SELECT t1.`Player`,
       t1.`Rank`,
       t1.`Previous_rank`,
       COALESCE(t2.`Rank`, 'irrelevant') AS opponent_rank
FROM      cte t1
LEFT JOIN cte t2
       ON t1.player <> t2.player
      AND t1.diff < 0 
      AND t2.diff < 0
ORDER BY t1.Player

The subquery (common table expression - cte) extracts the difference between the ranks, then we can apply a self join to the result of the previous query to match the players with negative difference.

For three players, this will work just fine.

Try it here.