MySQL update an existing table by iterating through inserted information to a different table -
i'll start indicating fact i'm new mysql triggers/functions/sps etc... therefore, if there mistake on how question being asked, please let me know , i'll try information missed.
i have following tables:
aos_matchs (matchid eventtime coalition initplayer initairplane event targplayer targairplane weapon hitcount msgid)
aos_players (matchid time era vs armament winner redplayers blueplayers)
aos_playerslist (name ace_factor)
aos_matchs.matchid = aos_players.matchid (matchid indication)
aos_matchs.initiator = aos_playerslist.name (linked player name)
aos_matchs.targplayer = aos_playerslist.name (linked player name)
table example:
aos_matchs:
matchid eventtime coalition initplayer initairplane event targplayer targairplane weapon hitcount msgid 56 00:00:00 red steve su-27 birth none none none none 1 56 00:00:00 red greg su-27 birth none none none none 2 56 00:00:00 blue gordon f-15c birth none none none none 3 56 00:00:00 blue george f-15c birth none none none none 4 56 00:00:54 blue george f-15c hit greg su-27 guns 36 5 56 00:01:09 red greg su-27 dead none none none none 6 56 00:01:13 red steve su-27 hit gordon f-15c guns 12 7 56 00:01:38 blue gordon f-15c dead none none none none 8 56 00:03:23 blue george f-15c hit steve su-27 guns 10 9 56 00:04:59 red steve su-27 hit george f-15c guns 8 10 56 00:05:08 blue george f-15c dead none none none none 11 74 00:00:00 red john f-15c birth none none none none 1 74 00:00:00 red adam f-15c birth none none none none 2 74 00:00:00 red michael f-15c birth none none none none 3 74 00:00:00 red bob f-15c birth none none none none 4 74 00:00:00 blue berry su-27 birth none none none none 5 74 00:00:00 blue jim f-15c birth none none none none 6 74 00:00:00 blue david su-27 birth none none none none 7 74 00:00:00 blue randy f-15c birth none none none none 8 74 00:00:48 red john f-15c hit jim f-15c guns 16 9 74 00:01:13 blue randy f-15c dead none none none none 10 74 00:01:37 red adam f-15c hit jim f-15c guns 10 11 74 00:01:46 red john f-15c dead none none none none 12 74 00:02:02 red bob f-15c dead none none none none 13 74 00:02:09 red michael f-15c hit david su-27 guns 52 14 74 00:02:19 blue david su-27 dead none none none none 15 74 00:02:30 red adam f-15c hit berry su-27 f-15c 1 16 74 00:02:30 blue berry su-27 hit adam f-15c su-27 1 17 74 00:02:30 red adam f-15c hit berry su-27 f-15c 1 18 74 00:02:30 blue berry su-27 hit adam f-15c su-27 1 19 74 00:02:36 red adam f-15c dead none none none none 20 74 00:03:38 red michael f-15c hit berry su-27 guns 48 21 74 00:03:39 blue berry su-27 dead none none none none 22 74 00:03:39 red michael f-15c hit berry su-27 guns 10 23 74 00:15:09 blue jim f-15c hit michael f-15c guns 40 24 74 00:15:18 red michael f-15c dead none none none none 25 aos_players:
matchid time era vs armament winner redplayers blueplayers 56 0000-00-00 00:00:00 modern 2vs2 guns red steve/greg gordon/george 74 0000-00-00 00:00:00 modern 4vs4 guns blue adam/john/michael/bob berry/jim/david/randy players
name ace_factor steve 1 john 1 adam 1 michael 1 david 1 bob 1 randy 1 gordon 1 george 1 greg 1 i catch trigger information being inserted match table run query relative info matchinfo table , iterate through query results. upon going through row row, deciding update players table subsequently.
what thought -
create trigger catch insert operations match table after info inserted.
in trigger, run query on relative matchinfo table , go through each row of query result , update players table accordingly.
appreciate trigger example above.
edit: barmars request, edited table original layout original information better understanding.
the aos_playerslist updating following trigger:
delimiter $$ use `mytable`$$ drop trigger /*!50032 if exists */ `updateplayerslist`$$ create /*!50017 definer = 'admin'@'%' */ trigger `updateplayerslist` before insert on `aos_matchs` each row begin insert ignore `aos_playerslist` (`name`,`ace_factor`) values (new.initplayer,1); end; $$ delimiter ; next update ace_factor in aos_playerslist table whenever new match inserted aos_matchs table, when happens, i'm trying trigger working update following query results -
select t1.initplayer,t1.percentage/t2.full_percentage 'kill_percent' ( select aos_matchs.matchid,aos_matchs.`initplayer`,sum(case aos_matchs.weapon when 'guns' aos_matchs.hitcount else 70 end) 'percentage',dtable.dead aos_matchs left join ( select matchid,initplayer dead aos_matchs `event`='dead') dtable on dtable.matchid=aos_matchs.`matchid` , dtable.dead=aos_matchs.targplayer aos_matchs.`event`='hit' , dtable.dead not null group aos_matchs.`matchid`,aos_matchs.`initplayer`,dtable.dead) t1 left join ( select aos_matchs.matchid,sum(case aos_matchs.weapon when 'guns' aos_matchs.hitcount else 70 end) 'full_percentage',dtable.dead aos_matchs left join ( select matchid,initplayer dead aos_matchs `event`='dead') dtable on dtable.matchid=aos_matchs.`matchid` , dtable.dead=aos_matchs.targplayer aos_matchs.`event`='hit' , dtable.dead not null group aos_matchs.`matchid`,dtable.dead) t2 on t1.matchid=t2.matchid , t1.dead=t2.dead t1.matchid = <matchid> , t1.dead = <the tested dead player> then, dead tested player , kill percentage of killer, update ace_factor column these rules -
if ace_factor1==ace_factor2 { if player_1 wins { new_acefactor1=ace_factor1+(ace_factor1/(ace_factor1+ace_factor2)); new_acefactor2=ace_factor2-(ace_factor1/(ace_factor1+ace_factor2)); } else if player_2 wins { new_acefactor1=ace_factor1-(ace_factor1/(ace_factor1+ace_factor2)); new_acefactor2=ace_factor2+(ace_factor1/(ace_factor1+ace_factor2)); } } else if ace_factor1>ace_factor2 { /*case 2a: stronger player (player 1) wins weaker (player 2) /* change in ace factor of 2 opponents determined ace factor of /*the weaker player if player_1 wins { new_acefactor1=ace_factor1+(ace_factor2/(ace_factor1+ace_factor2)); /*if ace factor of player 2 (loser) equals "1" remains "1" (no /*reduction) if ace_factor2==1 new_acefactor2=ace_factor2; else /* if ace factor of player 2 greater "1" reduced new_acefactor2=ace_factor2-(ace_factor2/(ace_factor1+ace_factor2)); } /* case 2b: weaker player (player 2) wins stronger player (player 1) else { /* in case new ace factor of weaker player (winner) determined /*(increased) value of ace factor of (stronger) opponent (loser) new_acefactor2=ace_factor2+(ace_factor1/(ace_factor1+ace_factor2)); /* in case new ace factor of stronger player (loser) determined /*(reduced) difference between 2 ace factors new_acefactor1=ace_factor1-((ace_factor1-ace_factor2)/(ace_factor1+ace_factor2)); } i hope clear enough, i'm looking trigger fetch query results each dead player , allow update of ace_factor column correctly according above rules.
here trigger i've been trying, it's not working did wrong i'm new triggers together:
delimiter $$ use `mytable`$$ create trigger `updateacefactor` after insert on `aos_players` each row begin create temporary table mplayers (pname varchar(255)); set @cid =0; set @mid = new.matchid; insert mplayers select initplayer aos_matchs matchid = @mid , `event`='dead'; while (select count(*) mplayers processed = 0)>0 select top 1 @cid = id mplayers processed = 0; declare initp varchar(255); declare initpace int; declare cur1 cursor ( select t1.initplayer,t1.percentage/t2.full_percentage 'kill_percent' ( select aos_matchs.matchid,aos_matchs.`initplayer`,sum(case aos_matchs.weapon when 'guns' aos_matchs.hitcount else 70 end) 'percentage',dtable.dead aos_matchs left join ( select matchid,initplayer dead aos_matchs `event`='dead') dtable on dtable.matchid=aos_matchs.`matchid` , dtable.dead=aos_matchs.targplayer aos_matchs.`event`='hit' , dtable.dead not null group aos_matchs.`matchid`,aos_matchs.`initplayer`,dtable.dead) t1 left join ( select aos_matchs.matchid,sum(case aos_matchs.weapon when 'guns' aos_matchs.hitcount else 70 end) 'full_percentage',dtable.dead aos_matchs left join ( select matchid,initplayer dead aos_matchs `event`='dead') dtable on dtable.matchid=aos_matchs.`matchid` , dtable.dead=aos_matchs.targplayer aos_matchs.`event`='hit' , dtable.dead not null group aos_matchs.`matchid`,dtable.dead) t2 on t1.matchid=t2.matchid , t1.dead=t2.dead t1.matchid = @mid , t1.dead = @cid) ktable; declare continue handler not found set done = true; open cur1; the_loop: loop fetch cur1 initp,initpace; if done leave the_loop; end if; if initpace == (select ace_factor `aos_playerslist` `name`=@cid) update `aos_playerslist` set ace_factor = (initpace+(initpace/initpace+(select ace_factor `aos_playerslist` `name`=@cid))); `name`=initp; if (select ace_factor `aos_playerslist` `name`=@cid) > 1 update `aos_playerslist` set ace_factor = ((select ace_factor `aos_playerslist` `name`=@cid)-(initpace/initpace+(select ace_factor `aos_playerslist` `name`=@cid))); `name`=@cid; end if; end if; if initpace > (select ace_factor `aos_playerslist` `name`=@cid) update `aos_playerslist` set ace_factor = (initpace+(initpace/initpace+(select ace_factor `aos_playerslist` `name`=@cid))); `name`=initp; if (select ace_factor `aos_playerslist` `name`=@cid) > 1 update `aos_playerslist` set ace_factor = ((select ace_factor `aos_playerslist` `name`=@cid)-(initpace/initpace+(select ace_factor `aos_playerslist` `name`=@cid))); `name`=@cid; end if; end if; if initpace < (select ace_factor `aos_playerslist` `name`=@cid) update `aos_playerslist` set ace_factor = (initpace+((select ace_factor `aos_playerslist` `name`=@cid)/(initpace+(select ace_factor `aos_playerslist` `name`=@cid)))); `name`=initp; update `aos_playerslist` set ace_factor = ((select ace_factor `aos_playerslist` `name`=@cid)-((initpace-(select ace_factor `aos_playerslist` `name`=@cid))/initpace+(select ace_factor `aos_playerslist` `name`=@cid))); `name`=@cid; end if; end loop the_loop; close cur1; update mplayers set processed = 1 id = @cid end; end; end$$ delimiter ;
Comments
Post a Comment