CODEWITHSUNDEEP
mysqlsqlsql-update
stack
stack

Reputation: 10228

Flipping the value of BIT data type and make it 1 for NULL value

I have a table like this:

// numbers
+---------+------------+
|    id   |    numb    |
+---------+------------+
| int(11) |   bit(1)   |
+---------+------------+
| 1       | 1          |
| 2       | 1          |
| 3       | 0          |
| 4       | NULL       |
+---------+------------+

And here is my query:

UPDATE numbers SET numb = numb ^ b'1';

And here is current output:

// numbers
+---------+------------+
|    id   |    numb    |
+---------+------------+
| int(11) |   bit(1)   |
+---------+------------+
| 1       | 0          |
| 2       | 0          |
| 3       | 1          |
| 4       | NULL       |
+---------+------------+

And here is expected output:

// numbers
+---------+------------+
|    id   |    numb    |
+---------+------------+
| int(11) |   bit(1)   |
+---------+------------+
| 1       | 0          |
| 2       | 0          |
| 3       | 1          |
| 4       | 1          |
+---------+------------+

As you see, All I'm trying to do is making 1 the result of NULL ^ b'1'. (current result is NULL). How can I do that?

Upvotes: 1

Views: 263

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726809

The task combines two problems:

  • Flipping a bit, and
  • Dealing with NULL

You can combine bit-toggling solution from one of your other Q&As with IFNULL for an easy-to-read solution:

UPDATE numbers SET numb = IFNULL(numb ^ b'1', 1)

This is a nearly word-for-word translation of your question:

  • "flip the value of bit" - numb ^ b'1'
  • "or set 1 if it's NULL" - IFNULL(..., 1)

Upvotes: 1

Related Questions