SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Update using CASE query problem

    Hi guys!

    Rather than fool around in PHP, I'm attempting to do some conditional work in a query:

    Code:
    UPDATE
        venues_clients
    SET
        venues_clients.commission =
    
    
        CASE
            WHEN
                venues_clients.commission <> ".$this->db->escape($array_parameters['commission'])."
            THEN
                ".$this->db->escape($array_parameters['commission'])."
        END,
    
    
        venues_clients.mode =
    
    
        CASE
            WHEN
                venues_clients.mode <> ".$this->db->escape($array_parameters['mode'])."
            THEN
                ".$this->db->escape($array_parameters['mode'])."
        END
    
    
    WHERE
        (venues_clients.client_id = " . $this->db->escape($array_parameters['client_id']) . ")
    AND
        (venues_clients.venue_id = " . $this->db->escape($array_parameters['venue_id']) . ")
    If I make a change to both commission (a text field on the web and float on the database) and mode (a select on the web and an enum on the database), everything works. But if I make no changes, the commission continues to work, while mode column becomes empty.

    Yes, the values are passing through the function with no problems.

    Any ideas where I've gone wrong?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Forbes View Post
    ... mode column becomes empty.
    because you're setting it to NULL

    whenever a CASE expression comparison evaluates as false, and there is no ELSE option, then ELSE defaults to NULL

    when you do not change the mode on the web page, then the "<>" is false

    instead, you should specify ELSE venues_clients.mode to set it to itself (which mysql is smart enought not to bother doing)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A head-slap moment if ever there was.

    R937, thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •