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?

Privide complete batch text, not a part of it. *syntax error from HeidiSQL* Provide complete error message. Ensure that this is MySQL, nott client, error message (for example, execute the same query via CLI). — Akina, Feb 18 '21 at 06:55

score 2 · Answer 1 · answered Feb 18 '21 at 06:55

You can't set a variable to multiple rows, just a single value.

You can join the table with a subquery that gets this value for each row.

UPDATE TABLE as t1
JOIN (
    SELECT id, SUBSTRING_INDEX(REFERENCE, '_', -1) AS suffix
    FROM TABLE
) AS t2 ON t1.id = t2.id
SET TYPE = CASE suffix
        WHEN 'A' THEN 1
        WHEN 'B' THEN 2
    END,
    COLOR = CASE suffix
        WHEN 'A' THEN 'BLUE'
        WHEN 'B' THEN 'RED'
    END
WHERE suffix IN ('A', 'B')

score 2 · Accepted Answer · answered Feb 18 '21 at 12:53

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.

I like this better than my version, too. – Barmar Mar 04 '21 at 02:20 — Barmar, Mar 04 '21 at 02:20

MySQL: Updating Query with user defined variable

2 Answers2