CODEWITHSUNDEEP
mysqlsqlsql-updateboolean
Zagstrug
Zagstrug

Reputation: 1729

MySql Updating boolean value

When I try to test manually updating the boolean value in my MySql table before implementing it into a ruby script, I get a match but no changes :

UPDATE calls SET ended = NOT ended WHERE incoming_Cid = '1234567890';

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 0

Unless I'm mistaken, this looks like correct syntax to me.

I looked at other threads concerning this issue (flipping boolean values) like here.

What would be a better way to accomplish this?

Upvotes: 4

Views: 8936

Answers (2)

Marc B
Marc B

Reputation: 360702

Most likely your ended value was an SQL NULL value. The logical not of a null value is still null, so the DB is properly reporting "no changes", because the value in ended didn't change - it started out as null, and was still null:

mysql> create table foo (x boolean);
mysql> insert into foo values (null);
Query OK, 1 row affected (0.04 sec)

mysql> update foo set x=not x;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update foo set x=not x;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Note the Changed: 0. But once you reset x to a non-null value:

mysql> update foo set x=true;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update foo set x=not x;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

rows start changing immediately.

Upvotes: 3

Frank
Frank

Reputation: 775

Would this work : 

UPDATE calls set ended = !ended WHERE incoming_Cid = '1234567890';

Upvotes: 4

Related Questions