CODEWITHSUNDEEP
mysql
jct
jct

Reputation: 69

MYSQL 5.7 UPDATE value with ternary (perform a true/false toggle)

I'm trying to toggle a table column between true and false. My most recent attempt--

UPDATE favorites SET favorited = (IF(favorited=true, 'false', 'true')) WHERE title="${recipe}"

--is giving me the following error:

ER_TRUNCATED_WRONG_VALUE: Truncated incorrect DOUBLE value: 'true'

Can any MYSQL geniuses guide me towards the correct syntax?

Upvotes: 0

Views: 121

Answers (2)

forpas
forpas

Reputation: 164099

If the data type of the column favorited is BOOLEAN then what you are doing is the negation of the current value so all you need is the operator NOT:

UPDATE favorites 
SET favorited = not favorited
WHERE title="${recipe}"

Upvotes: 0

RiggsFolly
RiggsFolly

Reputation: 94662

You are returning strings from the IF i.e. 'false', 'true' return booleans instead

UPDATE favorites 
    SET favorited = (IF(favorited=true, false, true)) .....

Small Note Genius not required in this case :)

Upvotes: 2

Related Questions