Ralph
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.
What am I missing here?
Thanks!
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 ;
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 ;
Parentheses don't seem to make a difference either.What am I missing here?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.