CODEWITHSUNDEEP
mysql
fmsf
fmsf

Reputation: 37147

MySQL: Simple way to toggle a value of an int field

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?

Upvotes: 53

Views: 28552

Answers (9)

George-Paul B.
George-Paul B.

Reputation: 586

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 */

Upvotes: 2

geethika
geethika

Reputation: 369

For ENUM(0,1) UPDATE some_table SET an_int_value = IF(an_int_value='1', '0', '1');

Upvotes: 0

Dave
Dave

Reputation: 386

For ENUM(0,1) fields you can use...

UPDATE table SET int_value=BINARY(int_value=1)

Upvotes: 0

Jayapal Chandran
Jayapal Chandran

Reputation: 11140

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... .

Upvotes: 4

FDisk
FDisk

Reputation: 9426

UPDATE table SET field = 1 - field

Upvotes: 120

Chad Birch
Chad Birch

Reputation: 74558

Another option:

UPDATE some_table SET an_int_value = ABS(an_int_value - 1);

Upvotes: 2

Eric Petroelje
Eric Petroelje

Reputation: 60498

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.

Upvotes: 12

Glavić
Glavić

Reputation: 43552

UPDATE some_table SET an_int_value = IF(an_int_value=1, 0, 1)

Upvotes: 16

vartec
vartec

Reputation: 134611

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

Upvotes: 91

Related Questions