4

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?

Community
  • 1
  • 1
Zagstrug
  • 1,669
  • 2
  • 11
  • 12
  • Would this work : UPDATE calls set ended = !ended – Frank Aug 08 '13 at 18:17
  • What's the type of the ended field? A quick test table here using `bit` is properly flipping the value – Marc B Aug 08 '13 at 18:22
  • 1
    Though @RahulTripathi has a query that works, yours is better Frank because it sets the Boolean 'ended' to the opposite that it currently is at, no matter what the circumstance. Rahul's just sets it to 0. Write it as an answer so I can pick it as my accepted answer! – Zagstrug Aug 08 '13 at 18:25
  • Marc, the ended field is `BOOLEAN`, and `SET ended = !ended` works! Don't know what `bit` is though :/ – Zagstrug Aug 08 '13 at 18:26
  • 1
    Quite puzzling ?!? Like @MarcB I've done a quick test and it works using your initial syntax: http://sqlfiddle.com/#!2/90fe2/1 – Sylvain Leroux Aug 08 '13 at 18:29
  • Yeah, I'm not quite sure why it didn't work out the first time, the syntax seemed correct. Nevertheless, it works now so I can modify the boolean value for ended calls with my call monitoring script. – Zagstrug Aug 08 '13 at 18:33
  • 1
    @Zagstrug: `boolean` is a synonym for `tinyint(1)`. could be something weird going on because a bit field is literally just a bit. boolean is actually an 8bit field that's limited to displaying only 1 digit (the `(1)` business) – Marc B Aug 08 '13 at 18:41

2 Answers2

4

Would this work : 

UPDATE calls set ended = !ended WHERE incoming_Cid = '1234567890';
Frank
  • 767
  • 5
  • 17
3

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.

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • Ohhh. That would explain the issue, because the ended is in fact a `NULL` value. So `not` of null would still be null ...Silly me... – Zagstrug Aug 08 '13 at 19:04
  • Yep. null is basically poison. Without taking special precautions, any operation involving null themselves become null. Treat it as "unknown", and when you mix known and unknown, you end up with unknown. – Marc B Aug 08 '13 at 19:18
  • So what happens if i have ended `BOOLEAN NOT NULL`? EDIT: Should I just change it to data type `int` and have it start at 0 so that when I do `ended = !ended` it goes from 0 (false) to 1 (true) – Zagstrug Aug 08 '13 at 19:21