MYSQL - Update values of new column based on condition

Question

I have the following table

+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| image_id     | int(11)    | YES  |     | NULL    |       |
| image_status | bit(3)     | YES  |     | NULL    |       |
| image_result | varchar(4) | YES  |     | NULL    |       |
+--------------+------------+------+-----+---------+-------+

image_id and image_status columns are populated with values. The values in image_result are all NULL.

I want to insert the following values into the image_result column based on the following conditions (I want to update all the rows in the table)-

if image_status = '0' OR image_status = '3' then image_result = 'Pass'
if image_status = '1' OR image_status = '4' then image_result = 'Warn'
if image_status = '2' then image_result = 'Fail'

How do I do the above?

Taha Paksu · Accepted Answer · 2012-10-09 10:58:13Z

8

UPDATE table 
SET image_result = CASE 
    WHEN image_status = 0 OR image_status = 3 THEN 'Pass' 
    WHEN image_status = 1 OR image_status = 4 THEN 'Warm' 
    ELSE 'Fail' 
END

answered Oct 9, 2012 at 10:58

Taha Paksu

15.6k2 gold badges50 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mahmoud Gamal · Accepted Answer · 2012-10-09 11:00:21Z

2

UPDATE TableName
SET image_result = CASE 
                     WHEN image_status = '0' OR image_status = '3' THEN 'PASS'  
                     WHEN image_status = '1' OR image_status = '4' THEN 'Warn' 
                     WHEN image_status = '2' THEN 'Fail'
                   END
WHERE image_status IN('0', '1', '2', '3', '4');

answered Oct 9, 2012 at 11:00

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Comments

Joe G Joseph · Accepted Answer · 2012-10-09 18:12:11Z

0

update <table> set image_result = 
Case when image_status = '0' OR image_status = '3' then 'Pass'
     when image_status = '1' OR image_status = '4' then 'Warn'
     when image_status = '2'                       then 'Fail'
End

edited Oct 9, 2012 at 18:12

answered Oct 9, 2012 at 10:57

Joe G Joseph

24.2k5 gold badges58 silver badges58 bronze badges

Collectives™ on Stack Overflow

MYSQL - Update values of new column based on condition

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related