CODEWITHSUNDEEP
mysqlsql
USERRR5
USERRR5

Reputation: 430

case expression to change the value of a column

I have two columns are measurement_unit and measurement_size.The values in measurement_unit column are ml,gm,litre.And measurement_size has the values 200,1300,2000i have converted all values of measurement_size to round figure such as .2,1.3,2 now i want to change the values of measurement_unit to liter or kg and here is my case expression .But it is not working..what should i do?In addition it is not a update query i am doing this for my report..it will not change any value in database..

 CASE WHEN MUC.name='ml' THEN (MUC.name)='Liter' 
    WHEN MUC.name='gm' THEN (MUC.name)='Kg' 
    END AS Measurement,

Upvotes: 0

Views: 58

Answers (2)

Vivek Khandelwal
Vivek Khandelwal

Reputation: 314

You can try this.

select 
CASE WHEN M_unit='ml' THEN 'Liter' 
    WHEN M_unit.name='gm' THEN 'Kg'
    ELSE M_UNIT 
    END AS Measurement_UNIT,
M_size/1000 as Measurement_SIZE
 from Table

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522396

One possibility would be to use a CASE WHEN inside an UPDATE statement:

UPDATE MUC
SET MUC.name = CASE
    WHEN MUC.name = 'ml' THEN 'Liter';
    WHEN MUC.name = 'gm' THEN 'Kg';
ELSE MUC.name
END

The only thing I don't like about this solution is that it will still update a row which does not match, using the current value. If you really only want to update the rows which you intend to convert, then you can try using two UPDATE statements, wrapped in a transaction:

START TRANSACTION
    UPDATE MUC
    SET MUC.name = 'Liter' WHERE MUC.name = 'ml'
    UPDATE MUC
    SET MUC.name = 'Kg' WHERE MUC.name = 'gm'
COMMIT

The transaction might be necessary if you want to ensure that no one ever sees your units in an intermediate state.

Upvotes: 1

Related Questions