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;
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.
END CASE;. Twice. dev.mysql.com/doc/refman/5.0/en/case.html