Commit | Line | Data |
---|---|---|
fb052bde SJ |
1 | -- Adds secondary education. |
2 | ||
3 | ALTER TABLE profile_education MODIFY COLUMN flags SET('primary', 'secondary', 'completed') NOT NULL DEFAULT ''; | |
4 | ||
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); | |
9 | ||
10 | -- Updates secondary education details. | |
11 | ||
12 | UPDATE profile_education | |
13 | SET degreeid = 33 | |
14 | WHERE FIND_IN_SET('secondary', flags) AND (degreeid = 4 OR degreeid = 6); | |
15 | ||
16 | UPDATE profile_education | |
17 | SET degreeid = 5 | |
18 | WHERE FIND_IN_SET('secondary', flags) AND degreeid IS NULL; | |
19 | ||
20 | UPDATE profile_education | |
21 | SET promo_year = grad_year | |
22 | WHERE FIND_IN_SET('secondary', flags); | |
23 | ||
24 | -- Removes duplicates. | |
25 | DELETE e1 | |
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; | |
30 | DELETE e1 | |
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; | |
35 | ||
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)) | |
39 | SET e1.id = 101 | |
40 | WHERE FIND_IN_SET('secondary', e1.flags) AND e1.degreeid = 33 AND e2.pid IS NULL; | |
41 | ||
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; | |
46 | ||
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)) | |
49 | SET e1.id = 101 | |
50 | WHERE FIND_IN_SET('secondary', e1.flags) AND e1.degreeid = 5 AND e2.pid IS NULL; | |
51 | ||
52 | UPDATE profile_education AS e1 | |
53 | INNER JOIN profile_education AS e2 ON (e1.pid = e2.pid AND e2.degreeid = 33) | |
54 | SET e1.id = 102 | |
55 | WHERE FIND_IN_SET('secondary', e1.flags) AND FIND_IN_SET('secondary', e2.flags) AND e1.degreeid = 5; | |
56 | ||
57 | -- vim:set syntax=mysql: |