Update using CASE query problem

[Forbes]

Hi guys!

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

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?

[r937]

... 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)

[Forbes]

A head-slap moment if ever there was.

R937, thanks!