-- Update fantasy football points

UPDATE passing_stats 
SET FFP = Yds /25 + TD*6 - Ints * 2 + 2pt *2;

UPDATE rushing_stats 
SET FFP = Yds / 10 + TD*6 + 2pt *2;

UPDATE receiving_stats 
SET FFP = Yds / 10 + TD*6 + 2pt *2;

UPDATE fumble_stats 
SET FFP = 0 - Lost * 2 + TD * 6;

UPDATE kicking_stats
SET FFP = 3 * 1_19 + 3 * 20_29 + 3 * 30_39 + 4 * 40_49 + 5 * 50p + XPG;


-- Update player 2003 ffp's...
DELETE from fantasy_points;

INSERT INTO fantasy_points ( fname, lname, dob, year, games )
SELECT p.fname, p.lname, p.dob, s.year, s.G
FROM 
  players p,
  passing_stats s
WHERE
  p.fname = s.fname and
  p.lname = s.lname and
  p.dob = s.dob

  and
  p.position = 'QB'
;

INSERT INTO fantasy_points ( fname, lname, dob, year, games )
SELECT p.fname, p.lname, p.dob, s.year, s.G
FROM 
  players p,
  rushing_stats s
WHERE
  p.fname = s.fname and
  p.lname = s.lname and
  p.dob = s.dob

  and
  p.position = 'RB'
;

INSERT INTO fantasy_points ( fname, lname, dob, year, games )
SELECT p.fname, p.lname, p.dob, s.year, s.G
FROM 
  players p,
  rushing_stats s
WHERE
  p.fname = s.fname and
  p.lname = s.lname and
  p.dob = s.dob

  and
  p.position = 'FB'
;

INSERT INTO fantasy_points ( fname, lname, dob, year, games )
SELECT p.fname, p.lname, p.dob, s.year, s.G
FROM 
  players p,
  receiving_stats s
WHERE
  p.fname = s.fname and
  p.lname = s.lname and
  p.dob = s.dob

  and
  p.position = 'WR'
;

INSERT INTO fantasy_points ( fname, lname, dob, year, games )
SELECT p.fname, p.lname, p.dob, s.year, s.G
FROM 
  players p,
  receiving_stats s
WHERE
  p.fname = s.fname and
  p.lname = s.lname and
  p.dob = s.dob

  and
  p.position = 'TE'
;

INSERT INTO fantasy_points ( fname, lname, dob, year, games )
SELECT p.fname, p.lname, p.dob, s.year, s.G
FROM 
  players p,
  kicking_stats s
WHERE
  p.fname = s.fname and
  p.lname = s.lname and
  p.dob = s.dob

  and
  p.position = 'K'
;



UPDATE fantasy_points f, passing_stats s
  SET f.points = f.points + s.FFP
WHERE
  f.fname = s.fname and
  f.lname = s.lname and
  f.dob   = s.dob and
  f.year  = s.year;

UPDATE fantasy_points f, rushing_stats s
  SET f.points = f.points + s.FFP
WHERE
  f.fname = s.fname and
  f.lname = s.lname and
  f.dob   = s.dob and
  f.year  = s.year;

UPDATE fantasy_points f, receiving_stats s
  SET f.points = f.points + s.FFP
WHERE
  f.fname = s.fname and
  f.lname = s.lname and
  f.dob   = s.dob and
  f.year  = s.year;

UPDATE fantasy_points f, kicking_stats s
  SET f.points = f.points + s.FFP
WHERE
  f.fname = s.fname and
  f.lname = s.lname and
  f.dob   = s.dob and
  f.year  = s.year;

UPDATE fantasy_points f, fumble_stats s
  SET f.points = f.points + s.FFP
WHERE
  f.fname = s.fname and
  f.lname = s.lname and
  f.dob   = s.dob and
  f.year  = s.year;

UPDATE fantasy_points SET ppg = points/games;