Strahinja Djurić Strahinja Djurić - 4 days ago 6
MySQL Question

Switch records in database

What i want to do is switch values of records in database. Here is an example:

+--------------+--------- +-------------------+
|ingredient_id | quantity | preferred_measure |
+--------------+----------+-------------------+
| 40 | 5.00 | tbsp |
| 28 | 5.00 | tsp |
+--------------+----------+-------------------+


What i want to do is to change preferred_measure and just switch them to look like this:

+--------------+--------- +-------------------+
|ingredient_id | quantity | preferred_measure |
+--------------+----------+-------------------+
| 40 | 5.00 | tsp |
| 28 | 5.00 | tbsp |
+--------------+----------+-------------------+


If i change just one then i will have same values for all records and i wouldn't be able to find records that need to be changed to other value because all will be same.

Keep in mind, count of these records in database is 10k+.

To clarify a little bit, preferred_measure is type of enum('tsp'.'tbsp'), it's not possible to change to some other value then this two.

Answer

this will only change the both strings:

UPDATE YOUR_TABLE SET preferred_measure =
  IF(preferred_measure = 'tsp', 'tbsp',
    IF (preferred_measure = 'tbsp', 'tsp', preferred_measure)); 

you also can use a WHERE to speedup the query:

UPDATE YOUR_TABLE SET preferred_measure =
  IF(preferred_measure = 'tsp', 'tbsp',
    IF (preferred_measure = 'tbsp', 'tsp', preferred_measure))
WHERE preferred_measure IN('tbsp','tsp'); 
Comments