CODEWITHSUNDEEP
mysqlsql
Chase W.
Chase W.

Reputation: 1413

Mysql Update Statement with Case Not Working

What am I doing wrong, I keep getting errors about syntax.

UPDATE common_lookup    
SET     common_lookup_column = 
CASE
  WHEN common_lookup_table = 'MEMBER' THEN
    CASE
      WHEN common_lookup_type IN ('INDIVIDUAL', 'GROUP') THEN
        common_lookup_table || '_TYPE'
      WHEN common_lookup_type LIKE '%CARD' THEN
       'CREDIT_CARD_TYPE'
    END
  ELSE
    common_lookup_table || '_TYPE'
END;

Upvotes: 0

Views: 104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

In MySQL, using the concat() function:

UPDATE common_lookup    
    SET common_lookup_column = (CASE WHEN common_lookup_table = 'MEMBER'
                                     THEN (CASE WHEN common_lookup_type IN ('INDIVIDUAL', 'GROUP')
                                                THEN concat(common_lookup_table, '_TYPE')
                                                WHEN common_lookup_type LIKE '%CARD'
                                                THEN 'CREDIT_CARD_TYPE'
                                           END)
                                     ELSE concat(common_lookup_table, '_TYPE')
                                END);

Assuming that you don't intend to have NULL values from the inner case, you can simplify this logic to:

UPDATE common_lookup    
    SET common_lookup_column = (CASE WHEN common_lookup_table = 'MEMBER' AND
                                          common_lookup_type LIKE '%CARD'
                                     THEN 'CREDIT_CARD_TYPE'
                                     ELSE concat(common_lookup_table, '_TYPE')
                                END);

The operation || is the string concatenation operator in several databases.

Upvotes: 2

Related Questions