MySQL: Simple way to toggle a value of an int field
MysqlMysql Problem Overview
I know how to do this, but i think I'll overcomplicate it with double selects and so on.
How can you do this (example in pseudo-sql)
UPDATE some_table SET an_int_value = (an_int_value==1 ? 0 : 1);
It must be an int value due to some other functionality, but how do you do it in a simple way?
Mysql Solutions
Solution 1 - Mysql
UPDATE table SET field = 1 - field
Solution 2 - Mysql
UPDATE some_table SET an_int_value = IF(an_int_value=1, 0, 1);
http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_if
Solution 3 - Mysql
UPDATE some_table SET an_int_value = IF(an_int_value=1, 0, 1)
Solution 4 - Mysql
In this case, you could use an XOR type operation:
UPDATE some_table SET an_int_value = an_int_value XOR 1
This is assuming that an_int_value will always be 1 or 0 though.
Solution 5 - Mysql
I can see the answers of all experienced people and i too got updated with their answers.
what about this... i do this way...
UPDATE tablename SET fieldname = not fieldname
can any body give suggestions please if this will not be a feasible solution. with respect to execution speed or any other... what to say... fact... concept... .
Solution 6 - Mysql
Another option:
UPDATE some_table SET an_int_value = ABS(an_int_value - 1);
Solution 7 - Mysql
If you're using TINYINT (0 and 1) then do a simply XOR (https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html#operator_xor)
UPDATE
`some_table`
SET
`an_int_value` = `an_int_value` ^ 1
Testing:
SELECT 0 ^ 1; /* returns 1 */
SELECT 1 ^ 1; /* returns 0 */
Solution 8 - Mysql
For ENUM(0,1) fields you can use...
UPDATE table SET int_value=BINARY(int_value=1)
Solution 9 - Mysql
For ENUM(0,1) UPDATE some_table SET an_int_value = IF(an_int_value='1', '0', '1');