CODEWITHSUNDEEP
mysql
Strahinja Djurić
Strahinja Djurić

Reputation: 404

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.

Upvotes: 0

Views: 47

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

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');

Upvotes: 1

Related Questions