-- 2009-05-01
UPDATE `contacts` SET grade_entrance_date = '2009-03-01', grade=grade+5 
WHERE deleted=0 AND smsgroup='student' AND date_entered='2009-04-27 12:20:33';
UPDATE `contacts` SET grade=grade-1 WHERE deleted=0 AND smsgroup='student' AND date_entered='2009-04-27 12:20:33';

-- 2009-07-06
UPDATE contacts SET contacts.lead_source = 'not_exist' 
WHERE ( contacts.lead_source IS NULL OR contacts.lead_source = '')  AND contacts.smsgroup = 'student';

-- 2009-07-07
UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE CONVERT( `sms_transactiontype`.`id` USING utf8 ) = '29' LIMIT 1 ;
UPDATE `sms_transactiontype` SET `deleted` = '1' WHERE CONVERT( `sms_transactiontype`.`id` USING utf8 ) = '5' LIMIT 1 ;

-- 2009-07-27
UPDATE exam_new, contacts SET exam_new.contact_id = contacts.id  WHERE exam_new.contact_id = contacts.idnumber;

INSERT INTO `exam_grades` (`id`, `contact_id`,`exam_id`,`subject_id`,`score`, `description`)
(SELECT id, contact_id, exam_id2, subject_id, grade, 'import'
FROM exam_new )
ON DUPLICATE KEY UPDATE score=VALUES(score);

-- 2009-11-30
UPDATE sms_fee SET period_no=3, due_to=3 WHERE contact_id IS NULL AND event_id IS NULL;

-- 2009-12-18
UPDATE contacts SET send_document_home='off', send_document_work='on' WHERE smsgroup='contact';
UPDATE contacts, users SET contacts.custom_var11='中居成子' WHERE contacts.deleted=0 AND contacts.smsgroup='contact'
AND contacts.assigned_user_id = users.id AND users.user_name='fshirai';

UPDATE contacts, users SET contacts.custom_var11='芙和せら' WHERE contacts.deleted=0 AND contacts.smsgroup='contact'
AND contacts.assigned_user_id = users.id AND users.user_name='masakai';

UPDATE contacts, users SET contacts.custom_var11='キャリアス' WHERE contacts.deleted=0 AND contacts.smsgroup='contact'
AND contacts.assigned_user_id = users.id AND users.user_name IN (
'hsagaitani',
'cakune',
'khasimoto',
'msako',
'mkawashima',
'nkuroda',
'nkatumata',
'fyamamoto',
'yhatanaka'
);

-- gakunen update on 2011-04-02
UPDATE contacts SET grade=grade+1 WHERE smsgroup='student' AND grade IS NOT NULL AND grade!=''
	AND grade NOT IN ('general', 'general1', 'general2', 'general3', 'eiken', '15') AND grade_entrance_date < '2011-04-01' ;
	
-- custom_var18 updates
SELECT last_name, first_name, idnumber, deleted, school_id, custom_var18 FROM `contacts` WHERE `custom_var18` LIKE 'junior' ;
UPDATE contacts SET custom_var18=NULL WHERE custom_var18 LIKE 'junior' ;