-- School
update contacts set school_id='sssssss-1111' where school_id='osaka';
update contacts set school_id='483794c4-adee-a607-f685-4441c943a1e2' where school_id='tokyo';
update contacts set school_id='58362bd0-5d1e-c3f9-efd1-4441c972601c' where school_id='nagoya';

-- Contact_status
update contacts set contact_status='11' where contact_status='A';
update contacts set contact_status='0' where contact_status='B';
update contacts set contact_status='7' where contact_status='C';

-- Email 
update contacts set email1=email2 where (email1 IS NULL OR email1='')  AND (email2 IS NOT NULL OR email1!='')
update contacts set email2=NULL where email1=email2;

-- Application_date and system id
update contacts, contacts2 set contacts.application_date= contacts2.application_date, contacts.id=contacts2.id,
 contacts.last_name = contacts2.last_name
WHERE contacts.idnumber=contacts2.idnumber;


-- Postalcode 
update contacts set primary_address_postalcode=replace(primary_address_postalcode,'-','') ;


-- Bring address from yubin database according to postalcode
update contacts,yubin set contacts.primary_state=yubin.ken, contacts.city = yubin.city, contacts.street=yubin.address
WHERE contacts.primary_address_postalcode=yubin.postcode 
AND LEFT(yubin.postcode,1) ='1';
update contacts,yubin set contacts.state=yubin.ken, contacts.city = yubin.city, contacts.street=yubin.address
WHERE contacts.primary_address_postalcode=yubin.postcode 
AND LEFT(yubin.postcode,1) ='2';
update contacts,yubin set contacts.state=yubin.ken, contacts.city = yubin.city, contacts.street=yubin.address
WHERE contacts.primary_address_postalcode=yubin.postcode 
AND LEFT(yubin.postcode,1) ='3';
update contacts,yubin set contacts.state=yubin.ken, contacts.city = yubin.city, contacts.street=yubin.address
WHERE contacts.primary_address_postalcode=yubin.postcode 
AND LEFT(yubin.postcode,1) ='4';
update contacts,yubin set contacts.state=yubin.ken, contacts.city = yubin.city, contacts.street=yubin.address
WHERE contacts.primary_address_postalcode=yubin.postcode 
AND LEFT(yubin.postcode,1) ='5';
update contacts,yubin set contacts.state=yubin.ken, contacts.city = yubin.city, contacts.street=yubin.address
WHERE contacts.primary_address_postalcode=yubin.postcode 
AND LEFT(yubin.postcode,1) ='6';
update contacts,yubin set contacts.state=yubin.ken, contacts.city = yubin.city, contacts.street=yubin.address
WHERE contacts.primary_address_postalcode=yubin.postcode 
AND LEFT(yubin.postcode,1) ='7';
update contacts,yubin set contacts.state=yubin.ken, contacts.city = yubin.city, contacts.street=yubin.address
WHERE contacts.primary_address_postalcode=yubin.postcode 
AND LEFT(yubin.postcode,1) ='8';
update contacts,yubin set contacts.state=yubin.ken, contacts.city = yubin.city, contacts.street=yubin.address
WHERE contacts.primary_address_postalcode=yubin.postcode 
AND LEFT(yubin.postcode,1) ='9';


update contacts,yubin set contacts.primary_address_state=yubin.ken, contacts.primary_address_city = yubin.city
WHERE contacts.primary_address_postalcode=yubin.postcode ;
update contacts set `primary_address_street` = replace(`primary_address_street`, `primary_address_state`, '') where 1;

update contacts set `primary_address_street` = replace(`primary_address_street`, `primary_address_city`, '') where 
LEFT(`primary_address_street`,2) = LEFT(`primary_address_city`,2)  ;


-- Replace ken and city in address with empty string 
update contacts set `primary_address_street` = replace(`primary_address_street`, `state`, '') where 1;

update contacts set `primary_address_street` = replace(`primary_address_street`, `city`, '') where 
LEFT(`primary_address_street`,2) = LEFT(`city`,2)  ;

-- Process checkbox
update contacts set custom_var4=0 where custom_var4='−';
update contacts set custom_var4=0 where custom_var4='ー';
update contacts set custom_var4=1 where custom_var4='○';

update contacts set custom_var5=0 where custom_var5='-';
update contacts set custom_var5=0 where custom_var5='−';
update contacts set custom_var5=0 where custom_var5='ー';
update contacts set custom_var5=1 where custom_var5='○';

update contacts set custom_var6=0 where custom_var6='-';
update contacts set custom_var6=0 where custom_var6='−';
update contacts set custom_var6=0 where custom_var6='ー';
update contacts set custom_var6=1 where custom_var6='○';

update contacts set custom_var7=0 where custom_var7='-';
update contacts set custom_var7=0 where custom_var7='−';
update contacts set custom_var7=0 where custom_var7='ー';
update contacts set custom_var7=1 where custom_var7='○';

update contacts set custom_var8=0 where custom_var8='-';
update contacts set custom_var8=0 where custom_var8='−';
update contacts set custom_var8=0 where custom_var8='ー';
update contacts set custom_var8=1 where custom_var8='○';

update contacts set custom_var9=0 where custom_var9='-';
update contacts set custom_var9=0 where custom_var9='−';
update contacts set custom_var9=0 where custom_var9='ー';
update contacts set custom_var9=1 where custom_var9='○';

update contacts set custom_var10=0 where custom_var10='-'
update contacts set custom_var10=0 where custom_var10='−';
update contacts set custom_var10=0 where custom_var10='ー';
update contacts set custom_var10=1 where custom_var10='○';

update contacts set custom_var11=0 where custom_var11='-'
update contacts set custom_var11=0 where custom_var11='−';
update contacts set custom_var11=0 where custom_var11='ー';
update contacts set custom_var11=1 where custom_var11='○';

update contacts set custom_var12=0 where custom_var12='-'
update contacts set custom_var12=0 where custom_var12='−';
update contacts set custom_var12=0 where custom_var12='ー';
update contacts set custom_var12=1 where custom_var12='○';

update contacts set custom_var13=0 where custom_var13='-';
update contacts set custom_var13=0 where custom_var13='−';
update contacts set custom_var13=0 where custom_var13='ー';
update contacts set custom_var13=1 where custom_var13='○';

update contacts set custom_var5=2 where custom_var5=1;
update contacts set custom_var6=4 where custom_var6=1;
update contacts set custom_var7=8 where custom_var7=1;
update contacts set custom_var8=16 where custom_var8=1;
update contacts set custom_var9=32 where custom_var9=1;
update contacts set custom_var10=64 where custom_var10=1;
update contacts set custom_var11=128 where custom_var11=1;
update contacts set custom_var12=256 where custom_var12=1;
update contacts set custom_var13=512 where custom_var13=1;

-- Calculate total kubun
update contacts set kubun_total=(custom_var4+custom_var5+custom_var6+custom_var7+custom_var8+
custom_var9+custom_var10+custom_var11+custom_var12+custom_var13);


-- move data
update contacts_temp,contacts set contacts_temp.id = contacts.id where 1;

insert into contacts_temp (id)  SELECT contacts.id  FROM contacts WHERE 1;


INSERT INTO `contacts_temp` (

`id` ,
`last_name` ,
`last_name_furigana` ,
`lead_source` ,
`phone_home` ,
`phone_mobile` ,
`phone_fax` ,
`email1` ,
`email2` ,
`primary_address_street` ,
`primary_address_city` ,
`primary_address_state` ,
`primary_address_postalcode` ,
`description` ,
`invalid_email` ,
`contact_status` ,
`school_id` ,
`send_document_home` ,
`idnumber` ,
`application_date` ,
`custom_var1` ,
`custom_var2` ,
`custom_var3` ,
`custom_var4` 

) SELECT contacts.id, contacts.last_name, contacts.last_name_furigana, contacts.lead_source, contacts.phone_home,
contacts.phone_mobile, contacts.phone_fax, contacts.email1, contacts.email2, contacts.primary_address_street,
contacts.city, contacts.state, contacts.primary_address_postalcode,  contacts.description, contacts.invalid_email,
contacts.contact_status, contacts.school_id, contacts.send_document_home, contacts.idnumber, contacts.application_date,
contacts.custom_var1, contacts.custom_var2, contacts.custom_var3, contacts.custom_var4  FROM contacts
where 1;


update contacts set smsgroup='student' where 1;
update contacts set date_entered=NOW(), date_modified=NOW() where 1;     
 

-- Set custom_var to proper locations like pulldown btw 11-18, checkbox 19-20
update contacts set custom_var19=custom_var4  where 1;
update contacts set custom_var11=custom_var2 where custom_var2!='';
update contacts set custom_var11=null where custom_var11=0;
update contacts set lead_source=null where lead_source=0;


-- Move Mail-Magazine checkbox to custom_var20
update contacts set custom_var20=send_document_home where 1;
update contacts set send_document_home='on' where 1;

-- Fix DM email requested by customer
update contacts set invalid_email=1 where contact_status='DM不' or contact_status='DＭ不'
OR contact_status='L' OR contact_status='Ｌ'
OR contact_status='ＤＭ不' or contact_status='ＤＭ絶';

-- Fix contact status 
update contacts set contact_status='11' where contact_status='' OR 
contact_status='DM不' or contact_status='DＭ不' OR contact_status='L' OR contact_status='Ｌ' OR 
contact_status='見込' OR contact_status='０５見' OR contact_status='Ａ' OR contact_status='ｑｗ' OR 
contact_status='ＤＭ不' or contact_status='ＤＭ絶';

update contacts set contact_status='0' where contact_status='Ｂ';
update contacts set contact_status='7' where contact_status='05春' OR contact_status='東京短' OR
contact_status='短期' ;

update contacts set contact_status='102' where contact_status='E';
update contacts set contact_status='103' where contact_status='F' OR
contact_status='提出' OR contact_status='提出他' OR contact_status='提出済';

update contacts set contact_status='104' where contact_status='G' OR 
contact_status='来期' OR contact_status='見込み' OR contact_status='Ｇ';

update contacts set contact_status='105' where contact_status='H' OR contact_status='参加' OR 
contact_status='参加　' OR contact_status='参加見' OR contact_status='参加８' OR contact_status='参加９' OR 
contact_status='見込　' OR contact_status='青梅' OR contact_status='Ｆ体' OR contact_status='Ｈ'; 
 
update contacts set contact_status='106' where contact_status='I' OR contact_status='０５春' OR
contact_status='Ｉ';
update contacts set contact_status='107' where contact_status='J' OR contact_status='小';
update contacts set contact_status='108' where contact_status='K' ;



SELECT DISTINCT (
contact_status
), count( contact_status ) 
FROM contacts
GROUP BY contact_status


-- Update main OSAKA main branch
update contacts set school_id='osaka' where school_id='483794c4-adee-a607-f685-4441c943a1e2' and deleted=0;
update contacts set school_id='483794c4-adee-a607-f685-4441c943a1e2' where school_id='sssssss-1111' and deleted=0;
update contacts set school_id='sssssss-1111' where school_id='osaka' and deleted=0;


update contacts set contact_status=5 where contact_status=11;
update contacts set contact_status=5 where contact_status=11;

-- 2007-11-22  metehan 

update c_m_ilk set custom_var4=0 where custom_var4='−';
update c_m_ilk set custom_var4=0 where custom_var4='ー';
update c_m_ilk set custom_var4=1 where custom_var4='○';

update c_m_ilk set custom_var5=0 where custom_var5='-';
update c_m_ilk set custom_var5=0 where custom_var5='−';
update c_m_ilk set custom_var5=0 where custom_var5='ー';
update c_m_ilk set custom_var5=1 where custom_var5='○';

update c_m_ilk set custom_var6=0 where custom_var6='-';
update c_m_ilk set custom_var6=0 where custom_var6='−';
update c_m_ilk set custom_var6=0 where custom_var6='ー';
update c_m_ilk set custom_var6=1 where custom_var6='○';

update c_m_ilk set custom_var7=0 where custom_var7='-';
update c_m_ilk set custom_var7=0 where custom_var7='−';
update c_m_ilk set custom_var7=0 where custom_var7='ー';
update c_m_ilk set custom_var7=1 where custom_var7='○';

update c_m_ilk set custom_var8=0 where custom_var8='-';
update c_m_ilk set custom_var8=0 where custom_var8='−';
update c_m_ilk set custom_var8=0 where custom_var8='ー';
update c_m_ilk set custom_var8=1 where custom_var8='○';

update c_m_ilk set custom_var9=0 where custom_var9='-';
update c_m_ilk set custom_var9=0 where custom_var9='−';
update c_m_ilk set custom_var9=0 where custom_var9='ー';
update c_m_ilk set custom_var9=1 where custom_var9='○';

update c_m_ilk set custom_var10=0 where custom_var10='-';
update c_m_ilk set custom_var10=0 where custom_var10='−';
update c_m_ilk set custom_var10=0 where custom_var10='ー';
update c_m_ilk set custom_var10=1 where custom_var10='○';

update c_m_ilk set custom_var11=0 where custom_var11='-';
update c_m_ilk set custom_var11=0 where custom_var11='−';
update c_m_ilk set custom_var11=0 where custom_var11='ー';
update c_m_ilk set custom_var11=1 where custom_var11='○';

update c_m_ilk set custom_var12=0 where custom_var12='-';
update c_m_ilk set custom_var12=0 where custom_var12='−';
update c_m_ilk set custom_var12=0 where custom_var12='ー';
update c_m_ilk set custom_var12=1 where custom_var12='○';

update c_m_ilk set custom_var13=0 where custom_var13='-';
update c_m_ilk set custom_var13=0 where custom_var13='−';
update c_m_ilk set custom_var13=0 where custom_var13='ー';
update c_m_ilk set custom_var13=1 where custom_var13='○';

update c_m_ilk set custom_var5=2 where custom_var5=1;
update c_m_ilk set custom_var6=4 where custom_var6=1;
update c_m_ilk set custom_var7=8 where custom_var7=1;
update c_m_ilk set custom_var8=16 where custom_var8=1;
update c_m_ilk set custom_var9=32 where custom_var9=1;
update c_m_ilk set custom_var10=64 where custom_var10=1;
update c_m_ilk set custom_var11=128 where custom_var11=1;
update c_m_ilk set custom_var12=256 where custom_var12=1;
update c_m_ilk set custom_var13=512 where custom_var13=1;

update c_m_ilk set custom_var19=(custom_var4+custom_var5+custom_var6+custom_var7+custom_var8+
custom_var9+custom_var10+custom_var11+custom_var12+custom_var13);

SELECT c.id, c.school_id, c.custom_var19, c2.idnumber FROM contacts c LEFT JOIN c_m_ilk c2 ON c.idnumber = c2.idnumber WHERE c2.idnumber IS NULL ;

SELECT c.id, c.idnumber, c.date_entered, c.date_modified, c.school_id, c.custom_var19, c2.idnumber FROM contacts c LEFT JOIN c_m_ilk c2 ON c.idnumber = c2.idnumber WHERE c2.id IS NULL ;


-- data is still same for custom_var19

SELECT c.id, c.idnumber, c.date_entered, c.date_modified, c.school_id, c.custom_var19, c2.idnumber FROM contacts c JOIN c_m_ilk c2 ON c.idnumber = c2.idnumber AND c.custom_var19 = c2.custom_var19 ;


SELECT c.id, c.idnumber, c.date_entered, c.date_modified, c.school_id, c.custom_var19, c2.idnumber FROM contacts c LEFT JOIN `contacts-2007-08-07` c2 ON c.idnumber = c2.idnumber WHERE c2.id IS NULL ;


-- wrong input (same value on different columns)
SELECT * FROM `c_m_new19` WHERE 
(k1=k2 AND k1!=0) OR (k1=k3 AND k1!=0) OR (k1=k4 AND k1!=0) OR (k1=k5 AND k1!=0) OR (k1=k6 AND k1!=0) OR (k1=k7 AND k1!=0) OR
(k2=k3 AND k2!=0) OR (k2=k4 AND k2!=0) OR (k2=k5 AND k2!=0) OR (k2=k6 AND k2!=0) OR (k2=k7 AND k2!=0) OR
(k3=k4 AND k3!=0) OR (k3=k5 AND k3!=0) OR (k3=k6 AND k3!=0) OR (k3=k7 AND k3!=0) OR
(k4=k5 AND k4!=0) OR (k4=k6 AND k4!=0) OR (k4=k7 AND k4!=0) OR
(k5=k6 AND k5!=0) OR (k5=k7 AND k5!=0) OR
(k6=k7 AND k6!=0)

-- get the new custom_var19 from the 講座１～講座７ from the initial data
UPDATE c_m_new19 SET custom_var19=65536 WHERE k1='16' OR k2='16' OR k3='16' OR k4='16' OR k5='16' OR k6='16' OR k7='16' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+32768 WHERE k1='15' OR k2='15' OR k3='15' OR k4='15' OR k5='15' OR k6='15' OR k7='15' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+16384 WHERE k1='14' OR k2='14' OR k3='14' OR k4='14' OR k5='14' OR k6='14' OR k7='14' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+8192 WHERE k1='13' OR k2='13' OR k3='13' OR k4='13' OR k5='13' OR k6='13' OR k7='13' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+4096 WHERE k1='12' OR k2='12' OR k3='12' OR k4='12' OR k5='12' OR k6='12' OR k7='12' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+2048 WHERE k1='11' OR k2='11' OR k3='11' OR k4='11' OR k5='11' OR k6='11' OR k7='11' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+1024 WHERE k1='10' OR k2='10' OR k3='10' OR k4='10' OR k5='10' OR k6='10' OR k7='10' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+512 WHERE k1='1000' OR k2='1000' OR k3='1000' OR k4='1000' OR k5='1000' OR k6='1000' OR k7='1000' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+256 WHERE k1='9' OR k2='9' OR k3='9' OR k4='9' OR k5='9' OR k6='9' OR k7='9' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+128 WHERE k1='8' OR k2='8' OR k3='8' OR k4='8' OR k5='8' OR k6='8' OR k7='8' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+64 WHERE k1='7' OR k2='7' OR k3='7' OR k4='7' OR k5='7' OR k6='7' OR k7='7' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+32 WHERE k1='6' OR k2='6' OR k3='6' OR k4='6' OR k5='6' OR k6='6' OR k7='6' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+16 WHERE k1='5' OR k2='5' OR k3='5' OR k4='5' OR k5='5' OR k6='5' OR k7='5' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+8 WHERE k1='4' OR k2='4' OR k3='4' OR k4='4' OR k5='4' OR k6='4' OR k7='4' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+4 WHERE k1='3' OR k2='3' OR k3='3' OR k4='3' OR k5='3' OR k6='3' OR k7='3' ;
UPDATE c_m_new19 SET custom_var19=custom_var19+2 WHERE k1='2' OR k2='2' OR k3='2' OR k4='2' OR k5='2' OR k6='2' OR k7='2' ;

UPDATE c_m_ilk cn, c_m_new19 co SET cn.custom19new=co.custom_var19 WHERE cn.idnumber=co.idnumber ;

UPDATE c_m_2new19 SET custom_var19=65536 WHERE k1='16' OR k2='16' OR k3='16' OR k4='16' OR k5='16' OR k6='16' OR k7='16' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+32768 WHERE k1='15' OR k2='15' OR k3='15' OR k4='15' OR k5='15' OR k6='15' OR k7='15' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+16384 WHERE k1='14' OR k2='14' OR k3='14' OR k4='14' OR k5='14' OR k6='14' OR k7='14' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+8192 WHERE k1='13' OR k2='13' OR k3='13' OR k4='13' OR k5='13' OR k6='13' OR k7='13' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+4096 WHERE k1='12' OR k2='12' OR k3='12' OR k4='12' OR k5='12' OR k6='12' OR k7='12' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+2048 WHERE k1='11' OR k2='11' OR k3='11' OR k4='11' OR k5='11' OR k6='11' OR k7='11' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+1024 WHERE k1='10' OR k2='10' OR k3='10' OR k4='10' OR k5='10' OR k6='10' OR k7='10' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+512 WHERE k1='1000' OR k2='1000' OR k3='1000' OR k4='1000' OR k5='1000' OR k6='1000' OR k7='1000' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+256 WHERE k1='9' OR k2='9' OR k3='9' OR k4='9' OR k5='9' OR k6='9' OR k7='9' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+128 WHERE k1='8' OR k2='8' OR k3='8' OR k4='8' OR k5='8' OR k6='8' OR k7='8' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+64 WHERE k1='7' OR k2='7' OR k3='7' OR k4='7' OR k5='7' OR k6='7' OR k7='7' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+32 WHERE k1='6' OR k2='6' OR k3='6' OR k4='6' OR k5='6' OR k6='6' OR k7='6' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+16 WHERE k1='5' OR k2='5' OR k3='5' OR k4='5' OR k5='5' OR k6='5' OR k7='5' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+8 WHERE k1='4' OR k2='4' OR k3='4' OR k4='4' OR k5='4' OR k6='4' OR k7='4' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+4 WHERE k1='3' OR k2='3' OR k3='3' OR k4='3' OR k5='3' OR k6='3' OR k7='3' ;
UPDATE c_m_2new19 SET custom_var19=custom_var19+2 WHERE k1='2' OR k2='2' OR k3='2' OR k4='2' OR k5='2' OR k6='2' OR k7='2' ;


-- set the custom_var which are not edited to the correct value, thus set to verified status and update the variable
SELECT c.id, c.idnumber, c.date_entered, c.date_modified, c.school_id, c.custom_var19, c2.idnumber FROM contacts c JOIN c_m_ilk c2 ON c.idnumber = c2.idnumber AND c.custom_var19 = c2.custom_var19 ;
UPDATE contacts c, c_m_ilk c2 SET c.custom_var16=3, c.custom_var19=c2.custom19new WHERE c.idnumber = c2.idnumber AND c.custom_var19 = c2.custom_var19 ;

UPDATE contacts c, c_m_new19 co SET c.custom_var15=co.custom_var19, c.custom_var16=1 WHERE c.idnumber=co.idnumber AND c.custom_var16 IS NULL ;
UPDATE contacts c, c_m_2new19 co SET c.custom_var15=co.custom_var19, c.custom_var16=2 WHERE c.idnumber=co.idnumber  AND c.custom_var16 IS NULL ;

SELECT c.id, c.idnumber, c.date_entered, c.date_modified FROM contacts c  WHERE c.custom_var16 IS NULL AND c.smsgroup = 'student' AND c.deleted =0 ;


-- the earliest record update date by a non-admin user is '2007-07-27 05:53:30'
-- so the record updated earlier than this date can be set to verified status
SELECT * FROM contacts c WHERE cdate_modified <  '2007-07-27 05:53:30' ;
UPDATE contacts c SET c.custom_var16=3, c.custom_var19=c.custom_var15 WHERE c.date_modified <  '2007-07-27 05:53:30' AND (c.custom_var16=1 OR c.custom_var16=2) ;

SELECT c.id, c.idnumber, c.date_entered, c.date_modified FROM contacts c  WHERE c.custom_var16 IS NULL AND c.smsgroup = 'student' AND c.deleted =0 ;
UPDATE contacts c  SET c.custom_var16=4 WHERE c.custom_var16 IS NULL AND c.smsgroup = 'student' AND c.deleted =0 ;

UPDATE contacts c, c_m_ilk c2 SET c.custom_var16=3, c.custom_var19=c2.custom19new WHERE c.date_modified <  '2007-07-27 05:53:30' ;

-- set the records of which value is somehow set by the customer same as the initial supposedly correct value, set them to verified status 
SELECT idnumber, date_entered, date_modified, modified_user_id, custom_var15, custom_var16, custom_var19  FROM `contacts` WHERE (custom_var16 = '1' OR custom_var16 = '2') AND deleted =0 AND smsgroup = 'student' AND custom_var15=custom_var19 ;
UPDATE contacts SET custom_var16=3  WHERE (custom_var16 = '1' OR custom_var16 = '2') AND deleted =0 AND smsgroup = 'student' AND custom_var15=custom_var19 ;

-- records of which value is not set at the second import operation
UPDATE contacts c SET c.custom_var16=3, c.custom_var19=c.custom_var15 WHERE c.date_modified='2007-08-07 00:00:00' AND c.custom_var19 IS NULL 

-- AFTER THESE OPERATIONS WE HAVE 266 records which are not verified yet!!!
SELECT c.id, c.idnumber, c.date_entered, c.date_modified, c.custom_var19, c.custom_var15 FROM contacts c  
WHERE (custom_var16 = '1' OR custom_var16 = '2')  AND c.smsgroup = 'student' AND c.deleted =0 ;