if col1 has value lorem or null
col2 - ipsum or null
col3 - dolor or null
col4 should be lorem,ipsum,dolor if there is no null inside preceding columns.
if, for example, col2 is null the result should be lorem,dolor
something like - update table set col4 = concat(col1, col2, col3) - but jumping over nulls
Is this possible using mysql?
You could use CONCAT_WS:
CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.
update table set col4 = concat_ws(',', col1, col2, col3);
To avoid running update every time I suggest to use generated column:
CREATE TABLE t(id INT, col1 TEXT, col2 TEXt, col3 TEXT,
col4 TEXT AS (concat_ws(',', col1, col2, col3))
)
