Link to home
Start Free TrialLog in
Avatar of Ralph
RalphFlag for United States of America

asked on

Two table update just isn't working in MySQL. No updates!

First the wide code.
I included a select that confirms the fields are set.  With / without the table qualifier doesn't make a difference, so it appears.
UPDATE   zModem_Census     z
     ,   ModemConfig       mc
   SET   z.have_plan_rates_set = 
           ( CASE WHEN NOT ISNULL(`mc`.`intl_rate_plan_id`)   AND NOT `mc`.`intl_rate_plan_id`=''   AND NOT `mc`.`intl_rate_plan_id`   = 'Unassigned' THEN 1 ELSE 0 END 
         AND CASE WHEN NOT ISNULL(`mc`.`dom_rate_plan_id`)    AND NOT `mc`.`dom_rate_plan_id`=''    AND NOT `mc`.`dom_rate_plan_id`    = 'Unassigned' THEN 1 ELSE 0 END )
  WHERE  z.modem_config_id = mc.modem_config_id
    AND  z.posting_period = 'Aug 2016'  ;

SELECT (CASE WHEN NOT ISNULL(`intl_rate_plan_id`)   AND NOT `intl_rate_plan_id`=''   AND NOT `intl_rate_plan_id`   = 'Unassigned' THEN 1 ELSE 0 END 
         AND CASE WHEN NOT ISNULL(`dom_rate_plan_id`)    AND NOT `dom_rate_plan_id`=''    AND NOT `dom_rate_plan_id`    = 'Unassigned' THEN 1 ELSE 0 END) AS HaveSet
FROM ModemConfig  ;

Open in new window

I've also (originally) tried using JOIN syntax:
  UPDATE zModem_Census     z
  JOIN   ModemConfig       mc
    ON   z.modem_config_id = mc.modem_config_id
   SET   have_plan_rates_set = 
             CASE WHEN NOT ISNULL(`intl_rate_plan_id`)   AND NOT `intl_rate_plan_id`=''   AND NOT `intl_rate_plan_id`   = 'Unassigned' THEN 1 ELSE 0 END 
         AND CASE WHEN NOT ISNULL(`dom_rate_plan_id`)    AND NOT `dom_rate_plan_id`=''    AND NOT `dom_rate_plan_id`    = 'Unassigned' THEN 1 ELSE 0 END
  WHERE  z.posting_period = var_posting_period  ;
  COMMIT ;

Open in new window

Parentheses don't seem to make a difference either.
What am I missing here?

Thanks!
ASKER CERTIFIED SOLUTION
Avatar of Ralph
Ralph
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial