Home > OS >  Can I use a trigger to update a value in a table using criteria from another row?
Can I use a trigger to update a value in a table using criteria from another row?

Time:06-04

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

Player Rank Previous_rank
A 954 977
B 1023 1000
C 1005 1015

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

Player Rank Previous_rank opponent_rank
A 954 977 1015
B 1023 1000 irrelevant
C 1005 1015 977

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:

Winner W_Score Loser L_Score
A 21 B 18
B 21 C 15
A 21 C 16

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.

  • Related