CODEWITHSUNDEEP
mysqlsql-update
Christopher Cooper
Christopher Cooper

Reputation: 1920

On field UPDATE if value is already SET, SET it to something else?

I know I can easily do this with a few lines of PHP but I was hoping there was a method in MYSQL to do this; I would think there is but I am not having any luck finding it.

Basically I want to do this:

UPDATE fieldName SET status='1'
if status='1', SET status='2'

So that second line is obviously not real code, but that is what I want to do. UPDATE a field to a value, and if that field already equals that value update it to a different value.

Upvotes: 2

Views: 2262

Answers (2)

Keeper
Keeper

Reputation: 3566

If you want to have a field that acts like a toggle button try this:

UPDATE table SET status = ABS(status - 1) WHERE...

This way every time you update the status toggles between 0 and 1

Upvotes: 1

Darryl Hein
Darryl Hein

Reputation: 144997

Would this work?

UPDATE table SET status = IF(status = 1, 2, 1) WHERE ...;

Basically, that's if status is equal to 1, then set it to 2 otherwise, leave it was 1. But then you might as well do, if that's all you want to do:

UPDATE table SET status = 2 WHERE status = 1;

Upvotes: 5

Related Questions