[RESOLVED] toggle with modulus

[Kudose]

'ello all.

I have the following query

UPDATE `gold_genres` SET `cat_status` = (`cat_status` + 1) % 2 WHERE `cat_id`='GUID_HERE' LIMIT 1

cat_status is enum(0,1)

It matches a row but does not update.

It is supposed to be a toggle essentially. if cat_status = 1, (1 + 1) % 2 = 0, else (0 + 1) % 2 = 1

Why?

TIA!

[sneakyimp]

My guess is that the enum value is stored as a string or something. Perhaps you could try

SET `cat_status` =  IF(cat_status=='1', 0, 1)

or something like that?

EDIT: link to mysql syntax...not sure what db you are using:
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

[Kudose]

Using the IF conditional created a syntax error I couldn't recover from.

I also tried to cast the column to unsigned, but it resulted in blank enum values being inserted ... even though (in phpMyAdmin) when I view the record, the enum has a value.

I ended up changing the enum to tinyint and used this query:

"UPDATE `".PREFIX."genres` SET `cat_status` = (`cat_status` + 1) % 2 WHERE `cat_id`='$guid' LIMIT 1