How to invert/flip the values of a TinyInt or Boolean column in MySQL

When someone creates a table in the database, usually the structure of the table should be easy to read for anyone, but depending on the experience of the developer it may become chaotic to work with it due to its hard mental mapping. Imagine a table where a column namely is_default whose possible values are 1 or 0 (true or false) e.g the following table human_values:

In this case, a human being must be honest, tolerant and patient, usually shouldn't hate anyone. Well, that's not a problem yet as you can understand the table, unless someone decide to change the logic of the form to be instead of is_default to is_optional. The values of the is_optional column still with the same value of the old is_default nomenclature. All the values now on the column would be wrong as they don't accomplish the new nomenclature. The logic would be now inverse:

According to the table now an human being must not be honest, tolerant or patient and should hate. To solve this you can easily use a query that inverts the value of the columns, for example, to flip all the is_optional column values of every row of the human_values table, you can use the following query syntax:

/** Invert all the values of the specified column **/
UPDATE tableName 
SET `your_boolean_or_tiny_int_field` = NOT `your_boolean_or_tiny_int_field`

To change the rows values, we are going to use the UPDATE statement indicating that a specific table should be modified. Then use the SET statement to define the new value of the field assigning its same value but prepending the NOT condition. NOT is the condition to negate, in this case it helps to invert the current value of the column e.g 0 would be now 1 and 1 would be 0.

For our table human_values the query should look like:

/** Invert all the values of the is_optional column **/
UPDATE human_values 
SET `is_optional` = NOT `is_optional`

Running the previous query would update all 4 rows of our table and leaving the following structure:

As you can see all zeros were converted to ones and viceversa. Remember that the example query modifies all the rows, you may add WHERE statements to limit the modified rows according to your needs.

Happy coding !