upgrade/1.1.1/07_yearpromo.sql

   1 ALTER TABLE profile_education ADD COLUMN promo_year INT(4) DEFAULT NULL AFTER grad_year;
   2
   3     UPDATE  profile_education AS pe
   4 INNER JOIN  profile_display   AS pd ON (pd.pid = pe.pid)
   5        SET  pe.promo_year = REPLACE(pd.promo, 'X', '')
   6      WHERE  pe.degreeid = 2 AND FIND_IN_SET('primary', pe.flags);
   7
   8     UPDATE  profile_education AS pe
   9 INNER JOIN  profile_display   AS pd ON (pd.pid = pe.pid)
  10        SET  pe.promo_year = REPLACE(pd.promo, 'M', '')
  11      WHERE  pe.degreeid = 33 AND FIND_IN_SET('primary', pe.flags);
  12
  13     UPDATE  profile_education AS pe
  14 INNER JOIN  profiles          AS p ON (p.pid = pe.pid)
  15        SET  pe.promo_year = REPLACE(SUBSTRING_INDEX(p.hrpid, '.', -1), 'd', '')
  16      WHERE  pe.degreeid = 5 AND FIND_IN_SET('primary', pe.flags);
  17
  18 -- vim:set syntax=mysql: