bloopiebloopie bloopiebloopie - 7 months ago 24
SQL Question

Error on MySQL trigger execution

So I have this trigger:

CREATE TRIGGER `onUpdatePlayerTracker` AFTER UPDATE ON `playtracker`
FOR EACH ROW UPDATE players
SET deckplayedmost = (SELECT deckname
FROM playTracker
WHERE playerID = old.playerID
AND amount = (SELECT max(amount)
FROM playtracker
WHERE playerID = old.playerID
LIMIT 1))
,

deckplayedleast =(SELECT deckname
FROM playTracker
WHERE playerID = old.playerID
AND amount = (SELECT min(amount)
FROM playtracker
WHERE playerID = old.playerID
LIMIT 1))


And when I try to update playtracker

PLAYTRACKER
PlayerID DeckName Amount
6 Space 1
4 Space 0
3 Space 1
5 Space 2
2 Space 1
1 Space 3
6 Sky 3
5 Sky 1
4 Sky 2
2 Sky 0
1 Sky 3
3 Sky 1
5 Ocean 3
4 Ocean 3
3 Ocean 0
1 Ocean 8
2 Ocean 4
6 Ocean 2
3 Mystic 2
2 Mystic 3
5 Mystic 2
6 Mystic 4
4 Mystic 1
1 Mystic 1
4 Forest 1
6 Forest 5
5 Forest 2
3 Forest 1
2 Forest 2
1 Forest 3
6 Desert 3
5 Desert 4
4 Desert 2
3 Desert 1
2 Desert 3
1 Desert 2


Which should trigger the trigger, I am getting an error saying the update couldn't complete as the result contained more then one row. It doesn't specify where the multiple return is coming from. It should update players table below.

Players
PlayerID PlayerName DeckPlayedMost DeckPlayedLeast FavCardType
1 Daniel Ocean Mystic Duel
2 Gavin Ocean Mystic Event
3 Patrick Ocean Mystic Exchange
4 Joe Ocean Mystic Attack
5 George Ocean Mystic Event
6 Robert Ocean Mystic Rares


This text is included as my post contains too much code and not enough of an explanation. Like I think I have provided enough explanation, I know it could be better, more detailed and whats not but I'm new to this. frankly I'm still surprised that people reply! I mean I'm rely grateful, learning can be such a difficult challenge, a real test of self and I am grateful to all of ye for yer help. Makes the stress of it all a little more manageable.

Answer

This will get rid of that error but I did not look to see if your query is correct otherwise.

SET deckplayedmost = (SELECT deckname 
                  FROM playTracker 
                  WHERE playerID =  old.playerID
                  AND  amount =  (SELECT max(amount)
                                    FROM playtracker
                                    WHERE playerID = old.playerID
                                    LIMIT 1)
                  LIMIT 1)
,

deckplayedleast =(SELECT deckname 
                  FROM playTracker 
                  WHERE playerID =  old.playerID
                  AND  amount = (SELECT min(amount)
                                    FROM playtracker
                                    WHERE playerID = old.playerID
                                    LIMIT 1)
                  LIMIT 1)