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
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_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 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 !