Solved

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

Posted on 2016-08-16
1
66 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
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now