-- 2013-12-30 1 contacts SELECT * FROM `contacts` WHERE `id` LIKE 'C' -- UPDATE contacts SET deleted=1 WHERE `id` LIKE 'C' 7 event_contacts SELECT event_contacts.id, event_contacts.contact_id AS student_id, event_contacts.event_id AS class_id, contacts.id AS student_id FROM event_contacts LEFT JOIN contacts ON event_contacts.contact_id=contacts.id WHERE event_contacts.deleted=0 AND contacts.id IS NULL ; -- 346 / 774,141 SELECT event_contacts.id, event_contacts.contact_id AS student_id, event_contacts.event_id AS class_id, smsevent.id AS class_id FROM event_contacts LEFT JOIN smsevent ON event_contacts.event_id=smsevent.id WHERE event_contacts.deleted=0 AND smsevent.id IS NULL -- 10 / 774,141 -- UPDATE event_contacts LEFT JOIN contacts ON event_contacts.contact_id=contacts.id SET event_contacts.deleted=1 WHERE event_contacts.deleted=0 AND contacts.id IS NULL -- UPDATE event_contacts LEFT JOIN smsevent ON event_contacts.event_id=smsevent.id SET event_contacts.deleted=1 WHERE event_contacts.deleted=0 AND smsevent.id IS NULL 8 smsevent SELECT smsevent.id course_id, smsevent.name, curriculum_id, curriculum.id FROM smsevent LEFT JOIN curriculum ON smsevent.curriculum_id=curriculum.id AND curriculum.deleted=0 WHERE category='course' AND smsevent.deleted=0 AND curriculum.id IS NULL -- 6,542 12 sms_event_instance SELECT sms_event_instance.parent_id as class_id, from_datetime, to_datetime, smsevent.name AS class_name, kamoku, curriculum_id FROM sms_event_instance LEFT JOIN smsevent ON smsevent.id=sms_event_instance.parent_id WHERE smsevent.id IS NULL -- 80 / 444,214 -- UPDATE sms_event_instance LEFT JOIN smsevent ON smsevent.id=sms_event_instance.parent_id SET sms_event_instance.deleted=1 WHERE smsevent.id IS NULL SELECT sms_event_instance_sub . * FROM sms_event_instance_sub LEFT JOIN sms_event_instance ON sms_event_instance.id = sms_event_instance_sub.instance_id WHERE sms_event_instance.id IS NULL -- 0 14 smsevent & sms_recurrence SELECT smsevent.id AS class_id, name AS class_name, kamoku, curriculum_id, actual_event_start, actual_event_end, actual_number_of_occurrences FROM smsevent LEFT JOIN sms_recurrence ON smsevent.id=sms_recurrence.id WHERE smsevent.deleted=0 AND sms_recurrence.id IS NULL -- 93,128 / 133,771 SELECT smsevent.id AS class_id FROM sms_recurrence LEFT JOIN smsevent ON smsevent.id=sms_recurrence.id WHERE smsevent.deleted=0 AND smsevent.id IS NULL -- 0 / 40,643 16 smsevent_instances SELECT smsevent_instances.event_id as class_id, target_id AS student_id, from_datetime, to_datetime, smsevent.name AS class_name, kamoku, curriculum_id FROM smsevent_instances LEFT JOIN smsevent ON smsevent.id=smsevent_instances.event_id WHERE smsevent.id IS NULL -- 7,308 / 3,755,095 SELECT smsevent_instances.* FROM smsevent_instances LEFT JOIN smsevent ON smsevent.id=smsevent_instances.event_id WHERE smsevent.id IS NULL -- for deleting SELECT smsevent_instances.event_id as class_id, target_id AS student_id, from_datetime, to_datetime, contacts.last_name AS student_name FROM smsevent_instances LEFT JOIN contacts ON contacts.id=smsevent_instances.target_id WHERE contacts.id IS NULL -- 0 / 3,755,095 SELECT smsevent_instances.* FROM smsevent_instances LEFT JOIN contacts ON contacts.id=smsevent_instances.target_id WHERE contacts.id IS NULL -- for deleting 17 sms_fee SELECT sms_fee.id, sms_fee.contact_id AS student_id, contacts.id AS student_id FROM sms_fee LEFT JOIN contacts ON sms_fee.contact_id=contacts.id WHERE sms_fee.deleted=0 AND contacts.id IS NULL ; -- 833 SELECT DISTINCT sms_fee.buppin_id FROM sms_fee LEFT JOIN buppin ON buppin.id = sms_fee.buppin_id WHERE sms_fee.buppin_id IS NOT NULL AND buppin.id IS NULL 18 sms_salesgroup SELECT sms_salesgroup.school_code, sms_transaction.school_id FROM sms_salesgroup, sms_transaction WHERE sms_salesgroup.salesno=sms_transaction.salesno AND sms_salesgroup.school_code!=sms_transaction.school_id -- UPDATE sms_salesgroup, sms_transaction SET sms_salesgroup.school_code=sms_transaction.school_id WHERE sms_salesgroup.salesno=sms_transaction.salesno AND sms_salesgroup.school_code!=sms_transaction.school_id　; -- 378件 19 sms_transaction SELECT sms_transaction.id, sms_transaction.contact_id AS student_id, contacts.id AS student_id FROM sms_transaction LEFT JOIN contacts ON sms_transaction.contact_id=contacts.id WHERE sms_transaction.deleted=0 AND contacts.id IS NULL ; -- 833 SELECT sms_fee.id, basic_fee, sell_date, sms_fee.contact_id AS student_id, contacts.id AS student_id FROM sms_fee LEFT JOIN contacts ON sms_fee.contact_id=contacts.id WHERE sms_fee.deleted=0 AND contacts.id IS NULL SELECT sms_salesgroup.id, sms_salesgroup.contact_id AS student_id, contacts.id AS student_id FROM sms_salesgroup LEFT JOIN contacts ON sms_salesgroup.contact_id=contacts.id WHERE sms_salesgroup.deleted=0 AND contacts.id IS NULL -- UPDATE sms_fee SET deleted=1 WHERE `basic_fee` IS NULL ; -- UPDATE sms_transaction SET deleted=1 WHERE `in_amount` IS NULL ; -- UPDATE sms_salesgroup SET deleted=1 WHERE `sell_date` IS NULL ; -- UPDATE sms_transaction LEFT JOIN contacts ON sms_transaction.contact_id=contacts.id SET sms_transaction.deleted=1 WHERE sms_transaction.deleted=0 AND contacts.id IS NULL -- 28 -- UPDATE sms_fee LEFT JOIN contacts ON sms_fee.contact_id=contacts.id SET sms_fee.deleted=1 WHERE sms_fee.deleted=0 AND contacts.id IS NULL -- 28-31 (?) -- UPDATE sms_salesgroup LEFT JOIN contacts ON sms_salesgroup.contact_id=contacts.id SET sms_salesgroup.deleted=1 WHERE sms_salesgroup.deleted=0 AND contacts.id IS NULL; -- 31 SELECT sms_fee.sell_date, sms_fee.deleted, sms_transaction.deleted, sms_fee.contact_id, sms_transaction.contact_id FROM sms_fee, sms_transaction WHERE sms_fee.salesno=sms_transaction.salesno AND sms_fee.contact_id!= sms_transaction.contact_id -- 445 latest sell_date is 2012-09-26 SELECT * FROM `sms_transaction` WHERE (koza_id IS NULL OR koza_id='') AND (buppin_id IS NULL OR buppin_id='') AND deleted=0 -- -- need confirmation (really necessary) SELECT id, buppin_category FROM `sms_transaction` WHERE buppin_category!='' -- UPDATE sms_transaction SET buppin_category=buppin_category+700000 WHERE buppin_category!='' 39 buppin SELECT buppin_code, count(*), FROM buppin GROUP BY buppin_code HAVING count(*)>1 SELECT sms_transaction.id, sell_date, t_date FROM sms_transaction, buppin WHERE sms_transaction.buppin_id=buppin.id AND buppin.deleted=1 AND sms_transaction.deleted=0 AND sell_date>='2010-10-01' ORDER BY sell_date DESC SELECT sms_transaction.* FROM sms_transaction, buppin WHERE sms_transaction.buppin_id=buppin.id AND buppin.deleted=1 AND sms_transaction.deleted=0 AND sell_date>='2010-10-01' ORDER BY sell_date DESC SELECT distinct buppin_id FROM sms_transaction, buppin WHERE sms_transaction.buppin_id=buppin.id AND buppin.deleted=1 AND sms_transaction.deleted=0 AND sell_date>='2010-10-01' ORDER BY sell_date DESC SELECT buppin.* FROM buppin, buppin_lookup WHERE buppin.id=buppin_lookup.id SELECT distinct contact_id FROM sms_transaction, buppin WHERE sms_transaction.buppin_id=buppin.id AND buppin.deleted=1 AND sms_transaction.deleted=0 AND sell_date>='2010-10-01' ORDER BY contact_id ASC -- buppin category problem SELECT id, buppin_category FROM `buppin` WHERE buppin_category IN ('6', '7') -- UPDATE buppin SET buppin_category=buppin_category+700000 WHERE buppin_category IN ('6', '7') -- UPDATE buppin SET buppin_category=700003 WHERE buppin_category IN ('70003') 40 hasso_contacts SELECT hasso_contacts.id, hassocode_contacts_id, hassocode_contacts.id as hassocode_contacts_id FROM hasso_contacts LEFT JOIN hassocode_contacts ON hasso_contacts.hassocode_contacts_id=hassocode_contacts.id WHERE hasso_contacts.deleted=0 AND hassocode_contacts.id IS NULL ; SELECT hasso_contacts.id, buppin_id, buppin.id AS bupin_id FROM hasso_contacts LEFT JOIN buppin ON hasso_contacts.buppin_id=buppin.id WHERE hasso_contacts.deleted=0 AND buppin.id IS NULL ; --SELECT hassocode_contacts_id, buppin_id, count(*) FROM hasso_contacts GROUP BY hassocode_contacts_id, buppin_id Having count(*)>1; 41-1 buppin_curriculum SELECT buppin_curriculum.id, buppin_id, buppin.id as buppin_id FROM buppin_curriculum LEFT JOIN buppin ON buppin_curriculum.buppin_id=buppin.id WHERE buppin_curriculum.deleted=0 AND buppin.id IS NULL ; 111KG21B1 111KG21B2 111KG21B3 131KG21B1 131KG21B3 S07SR320-11 SELECT buppin_curriculum.id, curriculum_id, curriculum.id as curriculum_id FROM buppin_curriculum LEFT JOIN curriculum ON buppin_curriculum.curriculum_id=curriculum.id WHERE buppin_curriculum.deleted=0 AND curriculum.id IS NULL ; --- 2,358 distinct curriculum, 19,471 records / Total: 25,409 SELECT buppin_id, curriculum_id, count(*) FROM buppin_curriculum GROUP BY buppin_id, curriculum_id Having count(*)>1; -- 0 41-2 buppin_class SELECT buppin_class.id, buppin_id, buppin.id as buppin_id FROM buppin_class LEFT JOIN buppin ON buppin_class.buppin_id=buppin.id WHERE buppin_class.deleted=0 AND buppin.id IS NULL ; SELECT buppin_class.id, event_id, smsevent.id as smsevent_id FROM buppin_class LEFT JOIN smsevent ON buppin_class.event_id=smsevent.id WHERE buppin_class.deleted=0 AND smsevent.id IS NULL ; 72,049 records, 12,187 unique class, TOTAL: 118,749 SELECT buppin_id, event_id, count(*) FROM buppin_class GROUP BY buppin_id, event_id Having count(*)>1; 42 curriculum_hassocode SELECT curriculum_hassocode.id, hassocode_id, curriculum_id, curriculum.id as curriculum_id FROM curriculum_hassocode LEFT JOIN curriculum ON curriculum_hassocode.curriculum_id=curriculum.id WHERE curriculum_hassocode.deleted=0 AND curriculum.id IS NULL ; -- 10,419 SELECT curriculum_hassocode.id, curriculum_id, hassocode_id, hassocode.id as hassocode_id FROM curriculum_hassocode LEFT JOIN hassocode ON curriculum_hassocode.hassocode_id=hassocode.id WHERE curriculum_hassocode.deleted=0 AND hassocode.id IS NULL ; SELECT hassocode_id, curriculum_id, count(*) FROM curriculum_hassocode GROUP BY hassocode_id, curriculum_id Having count(*)>1; 45 hassocode_buppin SELECT hassocode_buppin.id, buppin_id, hassocode_id, hassocode.id as hassocode_id FROM hassocode_buppin LEFT JOIN hassocode ON hassocode_buppin.hassocode_id=hassocode.id WHERE hassocode_buppin.deleted=0 AND hassocode.id IS NULL ; SELECT hassocode_buppin.id, hassocode_id, buppin_id, buppin.id as buppin_id FROM hassocode_buppin LEFT JOIN buppin ON hassocode_buppin.buppin_id=buppin.id WHERE hassocode_buppin.deleted=0 AND buppin.id IS NULL ; SELECT hassocode_id, buppin_id, count(*) FROM hassocode_buppin GROUP BY hassocode_id, buppin_id Having count(*)>1; 46 hassocode_hassobi SELECT hassocode_hassobi.id, hassobi_id, hassocode_id, hassocode.id AS hassocode_id FROM hassocode_hassobi LEFT JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id WHERE hassocode_hassobi.deleted=0 AND hassocode.id IS NULL ; 416 -- / 103,509 SELECT hassocode_hassobi.id, hassocode_id, hassobi_id, hassobi.id AS hassobi_id FROM hassocode_hassobi LEFT JOIN hassobi ON hassocode_hassobi.hassobi_id=hassobi.id WHERE hassocode_hassobi.deleted=0 AND hassobi.id IS NULL ; 2,633 -- / 103,509 SELECT count(*) , hassocode_id, hassobi_id FROM `hassocode_hassobi` WHERE 1 GROUP BY hassocode_id, hassobi_id 47 hassocode_contacts SELECT hassocode_contacts.id, contact_id, contacts.id as contact_id FROM hassocode_contacts LEFT JOIN contacts ON hassocode_contacts.contact_id=contacts.id WHERE hassocode_contacts.deleted=0 AND contacts.id IS NULL ; SELECT hassocode_contacts.id, hassocode_id, hassocode.id as hassocode_id FROM hassocode_contacts LEFT JOIN hassocode ON hassocode_contacts.hassocode_id=hassocode.id WHERE hassocode_contacts.hasso_type='1' AND hassocode_contacts.deleted=0 AND hassocode.id IS NULL ; SELECT hassocode_contacts.id, hassocode_id, shukka_kobetsu.id as hassocode_id FROM hassocode_contacts LEFT JOIN shukka_kobetsu ON hassocode_contacts.hassocode_id=shukka_kobetsu.id WHERE hassocode_contacts.hasso_type='2' AND hassocode_contacts.deleted=0 AND shukka_kobetsu.id IS NULL ; -- 1,210,819 SELECT hassocode_contacts.id, event_id, smsevent.id as event_id FROM hassocode_contacts LEFT JOIN smsevent ON hassocode_contacts.event_id=smsevent.id WHERE hassocode_contacts.hasso_type='1' AND hassocode_contacts.deleted=0 AND smsevent.id IS NULL ; -- 191,979 SELECT contact_id, hassocode_id, event_id, count(*) FROM hassocode_contacts GROUP BY contact_id, hassocode_id, event_id Having count(*)>1; -- updating teiki/nichiji setting SELECT hassocode.hassocode, hassocode.id as old_hassocode, hassocode.edaban, hassocode_contacts.id as hassocode_contact_id, hassobi.hassobi, hassocode_contacts.hassocode_contacts_hassobi as real_hassobi, concat(hassocode.hassocode, hassocode.edaban) as uniqu_id, hassocode_contacts.hasso_type, hassocode_contacts.teiki_nichiji, event_contacts.contact_id, hassocode.id as hassocode_id, smsevent.id as event_id, '1' as hasso_type, hassobi.extract_flg, event_contacts.end_date FROM event_contacts INNER JOIN smsevent ON event_contacts.event_id=smsevent.id AND smsevent.deleted=0 INNER JOIN hassobi ON smsevent.id=hassobi.event_id AND hassobi.deleted=0 INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id AND hassocode_hassobi.deleted=0 INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id AND hassocode.deleted=0 LEFT JOIN hassocode_contacts ON hassocode.id=hassocode_contacts.hassocode_id AND event_contacts.contact_id=hassocode_contacts.contact_id AND hassocode_contacts.deleted=0 WHERE event_contacts.deleted=0 AND hassocode_contacts.id IS NOT NULL AND hassobi.hassobi < hassocode_contacts.hassocode_contacts_hassobi UPDATE event_contacts INNER JOIN smsevent ON event_contacts.event_id=smsevent.id AND smsevent.deleted=0 INNER JOIN hassobi ON smsevent.id=hassobi.event_id AND hassobi.deleted=0 INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id AND hassocode_hassobi.deleted=0 INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id AND hassocode.deleted=0 LEFT JOIN hassocode_contacts ON hassocode.id=hassocode_contacts.hassocode_id AND event_contacts.contact_id=hassocode_contacts.contact_id AND hassocode_contacts.deleted=0 SET hassocode_contacts.teiki_nichiji=2 WHERE event_contacts.deleted=0 AND hassocode_contacts.id IS NOT NULL AND hassobi.hassobi < hassocode_contacts.hassocode_contacts_hassobi SELECT hassocode_contacts.hassocode_contacts_hassobi, hassobi.hassobi, hasso_type, teiki_nichiji FROM event_contacts INNER JOIN smsevent ON event_contacts.event_id=smsevent.id AND smsevent.deleted=0 INNER JOIN hassobi ON smsevent.id=hassobi.event_id AND hassobi.deleted=0 INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id AND hassocode_hassobi.deleted=0 INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id AND hassocode.deleted=0 LEFT JOIN hassocode_contacts ON hassocode.id=hassocode_contacts.hassocode_id AND event_contacts.contact_id=hassocode_contacts.contact_id AND hassocode_contacts.deleted=0 WHERE event_contacts.deleted=0 AND hassocode_contacts.id IS NOT NULL AND hassobi.hassobi > hassocode_contacts.hassocode_contacts_hassobi UPDATE event_contacts INNER JOIN smsevent ON event_contacts.event_id=smsevent.id AND smsevent.deleted=0 INNER JOIN hassobi ON smsevent.id=hassobi.event_id AND hassobi.deleted=0 INNER JOIN hassocode_hassobi ON hassocode_hassobi.hassobi_id=hassobi.id AND hassocode_hassobi.deleted=0 INNER JOIN hassocode ON hassocode_hassobi.hassocode_id=hassocode.id AND hassocode.deleted=0 LEFT JOIN hassocode_contacts ON hassocode.id=hassocode_contacts.hassocode_id AND event_contacts.contact_id=hassocode_contacts.contact_id AND hassocode_contacts.deleted=0 SET hassocode_contacts_hassobi = hassobi.hassobi WHERE event_contacts.deleted=0 AND hassocode_contacts.id IS NOT NULL AND hassobi.hassobi > hassocode_contacts.hassocode_contacts_hassobi 48 hassobi SELECT hassobi.id, event_id, smsevent.id AS event_id FROM hassobi LEFT JOIN smsevent ON hassobi.event_id=smsevent.id WHERE event_id!='' AND hassobi.deleted=0 AND smsevent.id IS NULL -- -- updating extract flag so that hasso_contacts will be created when saishinka is clicked SELECT * FROM hassobi WHERE deleted =0 AND hassobi > '2013-12-09' ; -- UPDATE hassobi SET extract_flg=1 WHERE deleted =0 AND hassobi > '2013-12-09' ; for zentairehearsal 2 date should be '2014-01-07', for honban date should be '2013-01-18' 50 shukka_kobetsu_buppin SELECT shukka_kobetsu_buppin.id, shukka_kobetsu_id, buppin_id FROM shukka_kobetsu_buppin LEFT JOIN shukka_kobetsu ON shukka_kobetsu_buppin.shukka_kobetsu_id=shukka_kobetsu.id WHERE shukka_kobetsu.id IS NULL -- 0 SELECT shukka_kobetsu_buppin.id, shukka_kobetsu_id, buppin_id, buppin.id AS buppin_id FROM shukka_kobetsu_buppin LEFT JOIN buppin ON shukka_kobetsu_buppin.buppin_id=buppin.id WHERE buppin.id IS NULL -- 50-1 shukka_kobetsu SELECT shukka_kobetsu.id, contact_id, contacts.id as contact_id FROM shukka_kobetsu LEFT JOIN contacts ON shukka_kobetsu.contact_id=contacts.id WHERE shukka_kobetsu.deleted=0 AND contacts.id IS NULL ; -- 147 SELECT contact_id, hassocode, count(*) FROM shukka_kobetsu GROUP BY contact_id, hassocode Having count(*)>1; -- UPDATE shukka_kobetsu LEFT JOIN contacts ON shukka_kobetsu.contact_id=contacts.id SET shukka_kobetsu.deleted=1 WHERE shukka_kobetsu.deleted=0 AND contacts.id IS NULL 50-2 shukka_kobetsu_buppin SELECT shukka_kobetsu_buppin.id, shukka_kobetsu_id, shukka_kobetsu.id as shukka_kobetsu_id FROM shukka_kobetsu_buppin LEFT JOIN shukka_kobetsu ON shukka_kobetsu_buppin.shukka_kobetsu_id=shukka_kobetsu.id WHERE shukka_kobetsu_buppin.deleted=0 AND shukka_kobetsu.id IS NULL ; -- 0 SELECT shukka_kobetsu_buppin.id, buppin_id, buppin.id as buppin_id FROM shukka_kobetsu_buppin LEFT JOIN buppin ON shukka_kobetsu_buppin.buppin_id=buppin.id WHERE shukka_kobetsu_buppin.deleted=0 AND buppin.id IS NULL ; SELECT shukka_kobetsu_id, buppin_id, count(*) FROM shukka_kobetsu_buppin GROUP BY shukka_kobetsu_id, buppin_id Having count(*)>1; -- UPDATE shukka_kobetsu_buppin LEFT JOIN buppin ON shukka_kobetsu_buppin.buppin_id=buppin.id SET shukka_kobetsu_buppin.deleted=1 WHERE shukka_kobetsu_buppin.deleted=0 AND buppin.id IS NULL 53 koza SELECT id, common_code, jukoukeitai_code, jukoukeitai_name, jukoukeitai, shikenshu FROM koza WHERE shikenshu IS NULL OR shikenshu='' -- 0 54 event_koza SELECT event_koza.id, event_id, koza_id, koza.id AS koza_id FROM event_koza LEFT JOIN koza ON event_koza.koza_id=koza.id WHERE event_koza.deleted=0 AND koza.id IS NULL ; -- 0 -- UPDATE event_koza LEFT JOIN koza ON event_koza.koza_id=koza.id SET event_koza.deleted=1 WHERE event_koza.deleted=0 AND koza.id IS NULL ; SELECT event_koza.id, koza_id, event_id, smsevent.id AS event_id FROM event_koza LEFT JOIN smsevent ON event_koza.event_id=smsevent.id WHERE event_koza.deleted=0 AND smsevent.id IS NULL ; -- 0 / 210,095 55 koza_contacts SELECT koza_contacts.id, koza_contacts.contact_id AS student_id, koza_contacts.koza_id AS koza_id, contacts.id AS student_id FROM koza_contacts LEFT JOIN contacts ON koza_contacts.contact_id=contacts.id WHERE koza_contacts.deleted=0 AND contacts.id IS NULL ; 150 -- 99,902 SELECT koza_contacts.id, koza_contacts.contact_id AS student_id, koza_contacts.koza_id AS koza_id, koza.id AS koza_id FROM koza_contacts LEFT JOIN koza ON koza_contacts.koza_id=koza.id WHERE koza_contacts.deleted=0 AND koza.id IS NULL; 886 -- SELECT koza_contacts.id, koza_contacts.contact_id AS student_id, koza_contacts.koza_id AS koza_id, contacts.id AS student_id, koza.id AS koza_id FROM koza_contacts LEFT JOIN contacts ON koza_contacts.contact_id=contacts.id LEFT JOIN koza ON koza_contacts.koza_id=koza.id WHERE koza_contacts.deleted=0 AND contacts.id IS NULL AND koza.id IS NULL; 2 -- --- UPDATE koza_contacts LEFT JOIN contacts ON koza_contacts.contact_id=contacts.id SET koza_contacts.deleted=1 WHERE koza_contacts.deleted=0 AND contacts.id IS NULL 57 kyoikukunren_koza SELECT kyoikukunren_koza.id, kunrenkoza_id, koza_id, koza.id AS koza_id FROM kyoikukunren_koza LEFT JOIN koza ON kyoikukunren_koza.koza_id=koza.id WHERE kyoikukunren_koza.deleted=0 AND koza.id IS NULL ; -- 3/ 5,158 58 kyoikukunren_contacs SELECT kyoikukunren_contacs.id, kunrenkoza_id, contact_id, contacts.id FROM kyoikukunren_contacs LEFT JOIN contacts ON kyoikukunren_contacs.contact_id=contacts.id WHERE kyoikukunren_contacs.deleted=0 AND contacts.id IS NULL ; -- 4 / 5,158 --- UPDATE kyoikukunren_contacs LEFT JOIN contacts ON kyoikukunren_contacs.contact_id=contacts.id SET kyoikukunren_contacs.deleted=1 WHERE kyoikukunren_contacs.deleted=0 AND contacts.id IS NULL 59 shiryo_fb_event_contacts SELECT shiryo_fb_event_contacts.id, contact_id, shiryo_fb_event_id, shiryo_fb_event.id AS shiryo_fb_event_id FROM shiryo_fb_event_contacts LEFT JOIN shiryo_fb_event ON shiryo_fb_event_contacts.shiryo_fb_event_id=shiryo_fb_event.id WHERE shiryo_fb_event_contacts.deleted=0 AND shiryo_fb_event.id IS NULL -- 0 SELECT shiryo_fb_event_contacts.id, shiryo_fb_event_id, contact_id, contacts.id AS contact_id FROM shiryo_fb_event_contacts LEFT JOIN contacts ON shiryo_fb_event_contacts.contact_id=contacts.id WHERE shiryo_fb_event_contacts.deleted=0 AND contacts.id IS NULL -- 1,325 SELECT shiryo_fb_event_contacts.id, shiryo_fb_event_id, shiryo_fb_event_category, shiryo_fb_event.id AS shiryo_fb_event_id, contact_id, contacts.id AS contact_id FROM shiryo_fb_event_contacts LEFT JOIN shiryo_fb_event ON shiryo_fb_event_contacts.shiryo_fb_event_id=shiryo_fb_event.id LEFT JOIN contacts ON shiryo_fb_event_contacts.contact_id=contacts.id WHERE shiryo_fb_event_contacts.deleted=0 AND contacts.id IS NULL AND shiryo_fb_event_category='event' SELECT shiryo_fb_event_contacts.id, shiryo_fb_event_id, shiryo_fb_event.id AS shiryo_fb_event_id, contact_id, contacts.id AS contact_id FROM shiryo_fb_event_contacts LEFT JOIN shiryo_fb_event ON shiryo_fb_event_contacts.shiryo_fb_event_id=shiryo_fb_event.id LEFT JOIN contacts ON shiryo_fb_event_contacts.contact_id=contacts.id WHERE shiryo_fb_event_contacts.deleted=0 AND contacts.id IS NULL AND shiryo_fb_event.id IS NULL -- 7,929 UPDATE shiryo_fb_event_contacts LEFT JOIN shiryo_fb_event ON shiryo_fb_event_contacts.shiryo_fb_event_id=shiryo_fb_event.id SET shiryo_fb_event_contacts.deleted=1 WHERE shiryo_fb_event_contacts.deleted=0 AND shiryo_fb_event.id IS NULL UPDATE shiryo_fb_event_contacts LEFT JOIN contacts ON shiryo_fb_event_contacts.contact_id=contacts.id SET shiryo_fb_event_contacts.deleted=1 WHERE shiryo_fb_event_contacts.deleted=0 AND contacts.id IS NULL 60 shiryo_fb_event fix SELECT shiryo_fb_event_category, shiryo_fb_event_group_id FROM shiryo_fb_event WHERE shiryo_fb_event_category='fb' UPDATE shiryo_fb_event SET shiryo_fb_event_group_id=1 WHERE shiryo_fb_event_category='fb' UPDATE shiryo_fb_event SET shiryo_fb_event_group_id=2 WHERE shiryo_fb_event_category='event' UPDATE shiryo_fb_event SET shiryo_fb_event_group_id=3 WHERE shiryo_fb_event_category='shiryo' SELECT hakkounengetsu, CONCAT(SUBSTRING(hakkounengetsu,1,4), SUBSTRING(hakkounengetsu,6,2)) FROM shiryo_fb_event WHERE shiryo_fb_event_category='shiryo' UPDATE shiryo_fb_event SET hakkounengetsu=CONCAT(SUBSTRING(hakkounengetsu,1,4), SUBSTRING(hakkounengetsu,6,2)) WHERE shiryo_fb_event_category='shiryo' UPDATE shiryo_fb_event SET hakkounengetsu=CONCAT(SUBSTRING(hakkounengetsu,1,4), SUBSTRING(hakkounengetsu,6,2)) WHERE shiryo_fb_event_category='fb' SELECT * FROM `shiryo_fb_event` WHERE shikenshu='131' SELECT * FROM `shiryo_fb_event` WHERE shikenshu='000' UPDATE shiryo_fb_event SET shikenshu='' WHERE shikenshu='131' ; UPDATE shiryo_fb_event SET shikenshu='G' WHERE shikenshu='000' ; UPDATE shiryo_fb_event SET school_id='1e0efc2d-f25f-f6cb-3fb8-50ecbf489027' WHERE `shiryo_fb_event_category` LIKE 'event' SELECT * FROM shiryo_fb_event WHERE id IN ('9900135687', '9900135688', '9900135689', '9900135704', '9900135792', '9900135796', '9900135797', '9900135810', '9900135866', '990136028') UPDATE shiryo_fb_event SET deleted=1 WHERE id IN ('9900135687', '9900135688', '9900135689', '9900135704', '9900135792', '9900135796', '9900135797', '9900135810', '9900135866', '990136028') SELECT spiral_number FROM `shiryo_fb_event` WHERE `shiryo_fb_event_category` LIKE 'shiryo' AND spiral_number=0 UPDATE shiryo_fb_event SET spiral_number=NULL WHERE spiral_number=0 -- AND `shiryo_fb_event_category` LIKE 'shiryo' UPDATE `itojuku_importz1`.`shiryo_fb_event` SET `deleted` = '1' WHERE CONVERT( `shiryo_fb_event`.`id` USING utf8 ) = '---' LIMIT 1 ; UPDATE `itojuku_importz1`.`shiryo_fb_event` SET `deleted` = '1' WHERE CONVERT( `shiryo_fb_event`.`id` USING utf8 ) = '/' LIMIT 1 ; UPDATE `itojuku_importz1`.`shiryo_fb_event` SET `deleted` = '1' WHERE CONVERT( `shiryo_fb_event`.`id` USING utf8 ) = '0' LIMIT 1 ; UPDATE `itojuku_importz1`.`shiryo_fb_event` SET `deleted` = '1' WHERE CONVERT( `shiryo_fb_event`.`id` USING utf8 ) = '0000' LIMIT 1 ; UPDATE `itojuku_importz1`.`shiryo_fb_event` SET `deleted` = '1' WHERE CONVERT( `shiryo_fb_event`.`id` USING utf8 ) = '0185' LIMIT 1 ; SELECT shiryo_fb_event_contacts.id FROM shiryo_fb_event_contacts INNER JOIN shiryo_fb_event ON shiryo_fb_event.id=shiryo_fb_event_contacts.shiryo_fb_event_id AND shiryo_fb_event.deleted=1 -- 0