csv - mysql update table if record not in temp table -
alright, have multiple mysql statements lead issue i'm having updating particular table. first let me show code, i'll explain i'm trying do:
/*step 1 - create temporary table temporarily store loaded csv*/ create temporary table if not exists `temptable1` `first60dayactivity`; /*step 2. load csv created temporary table*/ load data local infile '/users/me/downloads/some.csv' ignore table `{temptable}` fields terminated ',' optionally enclosed '\"' lines terminated '\r\n' ignore 1 lines set custid = 1030, created = now(), isactive = 1; /*step 3. update first60dayactivity table changing isactive records not in temptable*/ update `first60dayactivity` fa inner join `temptable1` temp on temp.`mid` = fa.`mid` , temp.`primarypartnername` = fa.`primarypartnername` , temp.`market` = fa.`market` , temp.`agedays` = fa.`agedays` , temp.`opendate` = fa.`opendate` , temp.`custid` = fa.`custid` set fa.isactive = if( temp.`mid` null, 0, 1 ); /*step 4. insert temp table records real table*/ .....blah blah blah..... ok, first create temporary table have table hold imported .csv data. next, import .csv data temporary table (all works far).
here run issue. i'm wanting update isactive column of each record of first60dayactivity table 0 if record not found in temptable1 (after import). ultimately, i'm gathering .csv, .csv has new live data should considered "active" , need set old data inactive. so, update inner join match on several column see if record found in temptable1, if isn't set activity 0, if found in temptable1 ensure activity status 1.
the problem here records in first60dayactivity retaining 1 property indicate active. nothing getting updated 0 though have proof new records exist within temptable1... can tell me i'm doing wrong in query?
thanks in advance!
temp.mid can never null because use column in join condition , use inner join.
your join (without insert) should return matching rows. using left join update should suppose want do.
Comments
Post a Comment