Change enum column automatically

Question

I have a column named status in a table which is of enum type and can have 2 values either 1 or 0. I want to write a query which will automatically check the current value of Enum and change to the other value.

For example, I have rows in table:

id   |  name   |  status
-----+---------+---------
  1  |  Abc    |    0
-----+---------+---------
  2  |  xyz    |    1

Now I want to write a mysql query where I pass the id of row to update and I will automatically check the current status and change it to the other. For example, if I pass id=1 it will automatically check the current value of status column and change it to 1. Similarly, if I pass id=2 it checks the status value of row 2 and changes it to 0.

show some data and expected output !!

Abhik Chakraborty
– Abhik Chakraborty

2014-04-10 08:36:58 +00:00
Commented Apr 10, 2014 at 8:36 — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 10, 2014 at 8:36

Alma Do · Accepted Answer · 2014-04-10 08:45:28Z

1

Here $id is your passed id. Note, that while bool operations (like status=!status) is "nifty", it's not good to work with ENUM-s such way.

UPDATE t SET status=IF(status='0', '1', '0') WHERE id=$id

also, good idea would be to change type to TINYINT if it's about storing 0 and 1 only (then status=!status will be good idea to use)

answered Apr 10, 2014 at 8:45

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Change enum column automatically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related