MYSQL Update Single column with different values

Question

There is this table "Trade" with column "call",Call contains values(C,P) when loaded from CSV I want to update Trade from java program such that if call='C' then call='CE' and

if call='P' then call='PE'

I figured out this can be done using 2 queries.like this

update Trade set call='CE' where call='C';

update Trade set call='PE' where call='P';

is there anyway this can be done in single query?

score 7 · Accepted Answer · answered Apr 12 '13 at 06:42

7

CALL is a reserved keyword and needs to be escaped.

UPDATE  Trade
SET     `CALL` = CASE   WHEN `Call` = 'C' THEN 'CE'
                        WHEN `Call` = 'P' THEN 'PE'
                    ELSE `CALL` END
WHERE   `CALL` IN ('C','P')

answered Apr 12 '13 at 06:42

John Woo

258,903
69
498
492

you need to escape `CALL` since it is a reserved keyword. `:D` – John Woo Apr 12 '13 at 06:45

score 5 · Answer 2 · answered Apr 12 '13 at 06:43

5

The shortest solution I see:

UPDATE Trade 
SET call = CONCAT(call,'E')
WHERE call IN ('C', 'P');

answered Apr 12 '13 at 06:43

sashkello

17,306
24
81
109

score 4 · Answer 3 · answered Apr 12 '13 at 06:41

4

Yes, you can do this in one query, using the CASE expression. Something like this:

update Trade 
set call = CASE 
             WHEN callputtype='C' THEN 'CE' 
             WHEN callputtype='P' THEN 'PE' 
           END
where callputtype IN ('c', 'P');

answered Apr 12 '13 at 06:41

Mahmoud Gamal

78,257
17
139
164

MYSQL Update Single column with different values

3 Answers3