1 -- Adds secondary education.
3 ALTER TABLE profile_education
MODIFY COLUMN flags
SET('primary', 'secondary', 'completed') NOT NULL DEFAULT '';
5 UPDATE profile_education
AS pe
6 INNER JOIN profile_education_enum
AS pen
ON (pe.eduid
= pen.
id)
7 SET pe.flags
= IF(pe.flags
= '', 'secondary', 'secondary,completed')
8 WHERE pen.abbreviation
= 'X' AND NOT FIND_IN_SET('primary', flags
);
10 -- Updates secondary education details.
12 UPDATE profile_education
14 WHERE FIND_IN_SET('secondary', flags
) AND (degreeid
= 4 OR degreeid
= 6);
16 UPDATE profile_education
18 WHERE FIND_IN_SET('secondary', flags
) AND degreeid
IS NULL;
20 UPDATE profile_education
21 SET promo_year
= grad_year
22 WHERE FIND_IN_SET('secondary', flags
);
24 -- Removes duplicates.
26 FROM profile_education
AS e1
27 INNER JOIN profile_education
AS e2
ON (e1.pid
= e2.pid
AND e1.degreeid
= e2.degreeid
AND e1.
id < e2.
id
28 AND FIND_IN_SET('secondary', e1.flags
) AND FIND_IN_SET('secondary', e2.flags
))
29 WHERE e1.fieldid
IS NULL AND e1.program
IS NULL;
31 FROM profile_education
AS e1
32 INNER JOIN profile_education
AS e2
ON (e1.pid
= e2.pid
AND e1.degreeid
= e2.degreeid
AND e1.
id > e2.
id
33 AND FIND_IN_SET('secondary', e1.flags
) AND FIND_IN_SET('secondary', e2.flags
))
34 WHERE e1.fieldid
IS NULL AND e1.program
IS NULL;
36 -- Updates secondary education ids.
37 UPDATE profile_education
AS e1
38 LEFT JOIN profile_education
AS e2
ON (e1.pid
= e2.pid
AND e1.degreeid
= e2.degreeid
AND e1.
id != e2.
id AND FIND_IN_SET('secondary', e2.flags
))
40 WHERE FIND_IN_SET('secondary', e1.flags
) AND e1.degreeid
= 33 AND e2.pid
IS NULL;
42 UPDATE profile_education
AS e1
43 INNER JOIN profile_education
AS e2
ON (e1.pid
= e2.pid
AND e1.degreeid
= e2.degreeid
AND e1.
id > e2.
id)
44 SET e1.
id = 101, e2.
id = 102
45 WHERE FIND_IN_SET('secondary', e1.flags
) AND FIND_IN_SET('secondary', e2.flags
) AND e1.degreeid
= 33;
47 UPDATE profile_education
AS e1
48 LEFT JOIN profile_education
AS e2
ON (e1.pid
= e2.pid
AND e2.degreeid
= 33 AND FIND_IN_SET('secondary', e2.flags
))
50 WHERE FIND_IN_SET('secondary', e1.flags
) AND e1.degreeid
= 5 AND e2.pid
IS NULL;
52 UPDATE profile_education
AS e1
53 INNER JOIN profile_education
AS e2
ON (e1.pid
= e2.pid
AND e2.degreeid
= 33)
55 WHERE FIND_IN_SET('secondary', e1.flags
) AND FIND_IN_SET('secondary', e2.flags
) AND e1.degreeid
= 5;
57 -- vim:set syntax=mysql: