| 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: |