CODEWITHSUNDEEP
mysql
Rob Taylor
Rob Taylor

Reputation: 91

Mysql Case - 2 conditions need same update value

I am trying to use a mysql case query to update multiple rows in a table. I have some cases which need to update the row with the same value. I was wondering whether it is possible to put all of these into one case or whether I have to create a new 'WHEN' for each?

Below is an example of what I am trying to accomplish but it obviously isn't the correct way to do this because I get an error.

UPDATE `groups` SET `status` = CASE `group_id` 
WHEN 32 OR WHEN 33 THEN '1' 
WHEN 31 THEN '2' 
END

Is it possible to do something like that?

Thanks

Upvotes: 0

Views: 1815

Answers (2)

xkeshav
xkeshav

Reputation: 54022

TRY

UPDATE `tablename` SET `status`= IF('group_id=31',2,1)

EDIT

UPDATE tableName SET `status` = IF( group_id IN (31, 32), 2, 1 ) WHERE section_id=1

OR

UPDATE tableName SET `status` = IF( group_id ANY (31, 32), 2, 1 ) WHERE section_id=1

running successfully on my table..what error u facing??

other syntax

UPDATE `tableName` SET `group_id` = CASE
WHEN group_id IN (31,32) THEN 1
WHEN group_id IN (33,34) THEN 2
END

Upvotes: 1

srgerg
srgerg

Reputation: 19329

I think you want

UPDATE groups SET status = CASE
    WHEN group_id = 32 OR group_id = 33 THEN '1'
    WHEN group_id = 31 THEN '2'
    END

Edit You can use operators like BETWEEN. For example

UPDATE groups SET status = CASE
    WHEN group_id BETWEEN 32 AND 33 THEN '1'
    WHEN group_id BETWEEN 30 AND 31 THEN '2'
    END

Upvotes: 3

Related Questions