I have several rows in a table and I am looking at a particular field called activeDeviceHandler. Some values are actual text and some values contain nothing i.e. "" (not a NULL value).
I want to update only the rows that have no value for the field. The query I am using is:
UPDATE activeDevices SET activeDeviceHandler="hello world"
WHERE activeDeviceHandler=""
This query executes as expected in MySQL Workbench but when I execute the query in PHP the values of my field all turn to null, regardless if they contain text or not.
This is extremely unusual. Can anyone help or suggest an alternative query? I have tried
UPDATE activeDevices SET activeDeviceHandler="hello world"
WHERE (LENGTH(activeDeviceHandler) < 1)
as well but this produces the same result.
What's the type of activeDeviceHandler? If it's an enum and you set it to a value that's not one of the enum members, you'll end up with a blank/null value instead. e.g.:
mysql> create table foo (x enum('a','b'));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into foo (x) values ('c'); // 'c' is not in the enum...
Query OK, 1 row affected, 1 warning (0.00 sec) // note the warning here
mysql> select x, length(x), isnull(x) from foo;
+------+-----------+-----------+
| x | length(x) | isnull(x) |
+------+-----------+-----------+
| | 0 | 0 |
+------+-----------+-----------+
1 row in set (0.00 sec)