Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-08-16
1
Medium Priority
?
94 Views
Last Modified: 2016-08-16
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!
0
Comment
Question by:Ralph
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 

Accepted Solution

by:
Ralph earned 0 total points
ID: 41758426
Never mind!  No Aug 2016 data yet.  I should have been using Aug 2015.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question