Update Player Scores

This process is in need of optimization.

Query a Player’s correct picks

select *
  from picks player_picks, picks perfect_picks, game g, team t
 where perfect_picks.pick   = player_picks.pick 
   and perfect_picks.game   = player_picks.game 
   and player_picks.game    = g.id
   and player_picks.pick    = t.id
   and perfect_picks.player = 1
   and player_picks.player  = 2;

select g.id, g.round, g.lower_seed, t.name, t.seed
  from picks player_picks, picks perfect_picks, game g, team t
 where perfect_picks.pick   = player_picks.pick 
   and perfect_picks.game   = player_picks.game 
   and player_picks.game    = g.id
   and player_picks.pick    = t.id
   and perfect_picks.player = 1
   and player_picks.player  = 2;

select g.id, g.round*(5 + g.lower_seed*t.seed) as points, g.lower_seed, g.round, t.seed
  from picks player_picks, picks perfect_picks, game g, team t 
 where perfect_picks.pick   = player_picks.pick 
   and perfect_picks.game   = player_picks.game 
   and player_picks.game    = g.id
   and player_picks.pick    = t.id
   and perfect_picks.player = 1
   and player_picks.player  = 2;

select  player_picks.player,
        sum(g.round*(5 + g.lower_seed*t.seed)) as points
  from picks player_picks, picks perfect_picks, game g, team t 
 where perfect_picks.pick   = player_picks.pick 
   and perfect_picks.game   = player_picks.game 
   and player_picks.game    = g.id
   and player_picks.pick    = t.id
   and perfect_picks.player = 1
   group by player_picks.player;

update player p, 
(
select  player_picks.player,
        sum(g.round*(5 + g.lower_seed*t.seed)) as points
  from picks player_picks, picks perfect_picks, game g, team t 
 where perfect_picks.pick   = player_picks.pick 
   and perfect_picks.game   = player_picks.game 
   and player_picks.game    = g.id
   and player_picks.pick    = t.id
   and perfect_picks.player = 1
   group by player_picks.player
)  pp
set p.points = pp.points
where p.id = pp.player
  and p.id <> 1
;

My tags:

 

Popular tags: