MySQL: Updating Query with user defined variable

Question

I am new to SQL and wasn't able to find a direct way of using User Defined Variable without using stored procedure. SUBSTRING_INDEX(REFERENCE) is being called multiple times and it looks inefficient. Below is sample code from my previous question.

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
             ...

Adding SET @rule_id = SUBSTRING_INDEX(CELERITY_MATCH_REF, '_', 1) FROM ilms_tran_cashflows_match at the top and using @rule_id resulted in syntax error from HeidiSQL.

What is the correct way or this is not possible?

Answer 1

I would actually suggest doing this by putting the values in a subquery:

UPDATE TABLE t JOIN
       (SELECT 'A' as SUFFIX, 1 as TYPE, 'BLUE' as COLOR UNION ALL
        SELECT 'B' as SUFFIX, 2 as TYPE, 'RED' as COLOR
       ) x
       ON SUBSTRING_INDEX(t.REFERENCE, '_', -1) = x.SUFFIX
    SET t.TYPE = x.type,
        t.COLOR = x.color;

This entirely removes the CASE expressions and simplifies the logic down to a single JOIN. It also puts all the values in one place, which should make it simpler to maintain and validate.