unable to update to default values null and empty string for tinyint and varchar fields

I have the following table schema -

CREATE TABLE `tablename` (
  `id` bigint(15) NOT NULL AUTO_INCREMENT,
  `uuid` varchar(400) NOT NULL,
  `pre_notif_action` varchar(30) DEFAULT '',
  `pre_notif_interval` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uuid_UNIQUE` (`uuid`),

  ) ENGINE=InnoDB DEFAULT CHARSET=latin1

For an existing record, with values "predeactivate" and 45 in the fields pre_notif_action and pre_notif_interval respectively -

mysql> select pre_notif_action, pre_notif_interval 
       from tablename 
       where uuid="1887826113857166800";

Result -

+------------------+--------------------+
| pre_notif_action | pre_notif_interval |
+------------------+--------------------+
| predeactivate    |                 45 |
+------------------+--------------------+

When I try to edit, I get non zero affected rows -

 update prepaid_account 
 set pre_notif_action="" 
     and pre_notif_interval=NULL 
 where  uuid="1887826113857166800";

Result -

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

However, when I select -

mysql> select pre_notif_action, pre_notif_interval 
       from prepaid_account 
       where uuid="1887826113857166800";

I get this output -

+------------------+--------------------+
| pre_notif_action | pre_notif_interval |
+------------------+--------------------+
| 0                |                 45 |
+------------------+--------------------+

How do I solve this?

Solution

I believe the issue here is the use of AND in the SET clause. I think your query is being interpreted like this:

update prepaid_account 
 set pre_notif_action = ("" and pre_notif_interval=NULL)
 where  uuid="1887826113857166800";

The ("" and pre_notif_interval=NULL) is being interpreted as a boolean, which is why 0 is inserted into the field (0 being the equivalent of boolean false in MySQL). To fix this, use a comma between multiple fields in the SET clause as follows:

update prepaid_account 
 set pre_notif_action = "", pre_notif_interval=NULL
 where  uuid="1887826113857166800";