MySQL: Updating multiple columns using if else

Question

I need help to set a mySQL update query for my little project like below:

UPDATE TABLE 
IF SUBSTRING_INDEX(REFERENCE, '_', -1)= 'A'  THEN
{
  TYPE = 1
  COLOR = 'BLUE'
}
ELSEIF SUBSTRING_INDEX(REFERENCE, '_', -1)= 'B'  THEN
{
  TYPE = 2
  COLOR = 'RED'
},
ID = SUBSTRING_INDEX(REFERENCE, '_', 1)
WHERE REFERENCE IS NOT NULL

In my table, there is a column REFERENCE which contains varchar like

A_1, A_2, A_10
B_1, B_4, B_12
etc...

where the character before the symbol '_' determines certain values for other columns like stated above. I am still fairly new to mySQL, so appreciates any help!

score 2 · Accepted Answer · answered Feb 18 '21 at 03:32

You can use case expressions:

UPDATE TABLE 
    SET TYPE = (CASE WHEN SUBSTRING_INDEX(REFERENCE, '_', -1) = 'A'  
                     THEN 1
                     WHEN SUBSTRING_INDEX(REFERENCE, '_', -1) = 'B'  
                     THEN 2
                     ELSE TYPE
                END),
        COLOR = (CASE WHEN SUBSTRING_INDEX(REFERENCE, '_', -1) = 'A'  
                      THEN 'BLUE'
                      WHEN SUBSTRING_INDEX(REFERENCE, '_', -1) = 'B'  
                      THEN 'RED'
                      ELSE COLOR
                 END),
        ID = SUBSTRING_INDEX(REFERENCE, '_', 1)
    WHERE REFERENCE IS NOT NULL;

MySQL: Updating multiple columns using if else

1 Answers1