Is there a way in MySQL to reverse a boolean field with one query?

Question

I have a column in my table titled 'authorised'. It's default is 0. It needs to be changed to 1 when the user is authorised, but it must be able to be reset to 0. I know I could do this easily with 2 queries like so:

$authorised = Db::query('SELECT authorised FROM users WHERE id=2');

$newAuthValue = ($authorised['authorised']) ? 0 : 1;

Db::query('UPDATE users SET authorised=' . $newAuthValue . ' WHERE id=2');

What I wanted to know, is there a way to do this with one query? To reverse a boolean value? I think MySQL might not have a true Boolean data type, as when I make it under phpMyAdmin it just becomes tinyint(1).

Answer 1

UPDATE users SET `authorised` = IF (`authorised`, 0, 1)

Answer 2

There are multiple ways to do this, here's a simple one:

UPDATE users SET authorised = ABS(authorised - 1) WHERE id = 2;

Answer 3

UPDATE `users` SET `authorised` = NOT `authorised` WHERE id = 2

This query will also work to negate the field, and is more inline with boolean syntax.