| 1 | -- Deletes lost addresses, medals and phones. |
| 2 | DELETE a |
| 3 | FROM profile_addresses AS a |
| 4 | WHERE a.type = 'home' AND NOT EXISTS (SELECT * |
| 5 | FROM profiles AS p |
| 6 | WHERE a.pid = p.pid); |
| 7 | DELETE a |
| 8 | FROM profile_addresses AS a |
| 9 | WHERE a.type = 'job' AND NOT EXISTS (SELECT * |
| 10 | FROM profile_job AS j |
| 11 | WHERE a.pid = j.pid AND a.id = j.id); |
| 12 | DELETE a |
| 13 | FROM profile_addresses AS a |
| 14 | WHERE a.type = 'hq' AND NOT EXISTS (SELECT * |
| 15 | FROM profile_job_enum AS je |
| 16 | WHERE a.jobid = je.id); |
| 17 | DELETE a |
| 18 | FROM profile_addresses AS a |
| 19 | WHERE a.type = 'group' AND NOT EXISTS (SELECT * |
| 20 | FROM groups AS g |
| 21 | WHERE a.groupid = g.id); |
| 22 | DELETE m |
| 23 | FROM profile_medals AS m |
| 24 | WHERE m.gid != 0 AND NOT EXISTS (SELECT * |
| 25 | FROM profile_medal_grade_enum AS mg |
| 26 | WHERE m.mid = mg.mid AND m.gid = mg.gid); |
| 27 | DELETE p |
| 28 | FROM profile_phones AS p |
| 29 | WHERE p.link_type = 'address' AND NOT EXISTS (SELECT * |
| 30 | FROM profile_addresses AS a |
| 31 | WHERE p.pid = a.pid AND p.link_id = a.id); |
| 32 | DELETE p |
| 33 | FROM profile_phones AS p |
| 34 | WHERE p.link_type = 'pro' AND NOT EXISTS (SELECT * |
| 35 | FROM profile_job AS j |
| 36 | WHERE p.pid = j.pid AND p.link_id = j.id); |
| 37 | DELETE p |
| 38 | FROM profile_phones AS p |
| 39 | WHERE p.link_type = 'user' AND NOT EXISTS (SELECT * |
| 40 | FROM profiles AS pf |
| 41 | WHERE p.pid = pf.pid); |
| 42 | DELETE p |
| 43 | FROM profile_phones AS p |
| 44 | WHERE p.link_type = 'hq' AND NOT EXISTS (SELECT * |
| 45 | FROM profile_job_enum AS je |
| 46 | WHERE p.link_id = je.id); |
| 47 | DELETE p |
| 48 | FROM profile_phones AS p |
| 49 | WHERE p.link_type = 'group' AND NOT EXISTS (SELECT * |
| 50 | FROM groups AS g |
| 51 | WHERE p.link_id = g.id); |
| 52 | |
| 53 | -- vim:set syntax=mysql: |