How to swap-update values in a field

Question

These two links are related to this question I guess, but I do not find a way out to solve my requirement which is slightly different.

I have a table, say like this:

fruit         color
--------------------
mango          red
apple          red
orange         yellow
banana         green

I need to UPDATE field color in such a way that red becomes yellow and yellow becomes red. Here is how the table should look like after update query.

fruit         color
--------------------
mango          yellow
apple          yellow
orange         red
banana         green

This query wont work obviously.

UPDATE plant SET color = 'yellow' WHERE color = 'red';
UPDATE plant SET color = 'red' WHERE color = 'yellow'

Thanks.

You're updating `fruit` in your example query, not `color` – lanzz Jun 03 '12 at 07:28 — lanzz, Jun 03 '12 at 07:28
@lanzz oh silly error, let me correct it.. – nawfal Jun 03 '12 at 07:30 — nawfal, Jun 03 '12 at 07:30

score 4 · Answer 1 · answered Jun 03 '12 at 07:33

4

UPDATE plant SET color = CASE color
    WHEN 'yellow' THEN 'red'
    WHEN 'red'    THEN 'yellow'
  END
WHERE color IN ('red', 'yellow')

answered Jun 03 '12 at 07:33

eggyal

122,705
18
212
237

Thank eggyal, I like this answer better. – nawfal Jun 03 '12 at 07:38

score 1 · Accepted Answer · edited Oct 01 '12 at 09:15

1

You can do it in a single query:

UPDATE plant 
SET    color = IF(color = 'yellow', 'red', IF(color = 'red', 'yellow', color))
WHERE  color IN ('red', 'yellow');

edited Oct 01 '12 at 09:15

nawfal

70,104
56
326
368

answered Jun 03 '12 at 07:29

lanzz

42,060
10
89
98

Just what I was about to say! :) Although I'd be tempted to use a single `CASE` expression rather than the nested `IF` - but is only for readability, really. – eggyal Jun 03 '12 at 07:30
I tend to avoid `CASE` when the choices are few, the additional noise keywords make for a less-readable query in my eyes. – lanzz Jun 03 '12 at 07:32

How to swap-update values in a field

2 Answers2