CODEWITHSUNDEEP
mysqlconcatenationstring-concatenation
gurluk
gurluk

Reputation: 195

MySQL - Add text or texts to a row if conditions are satisfied

Sorry if the question is basic, I am learning MySQL.

Let's say I have a table like this:

+---+---+-----+
|A  |B  |Con  |
+---+---+-----+
|3  |3  |     |
|2  |3  |     |
|3  |2  |     |
|2  |2  |     |
+---+---+-----+

I want to update the Con column based on a condition, say add the column name if column is smaller than 3. Instead of writing every condition, I want to add a text to the column with concat function, if a condition is satisfied:

update table
    set Con = 
        case 
            when A < 3
                then concat(' A ')
            when B < 3 
                then concat(' B ')
        end

So, I want something like this: 

+---+---+-----+
|A  |B  |Con  |
+---+---+-----+
|3  |3  |     |
|2  |3  |A    |
|3  |2  |B    |
|2  |2  |A B  |
+---+---+-----+

But, of course this does not work since case function is called once and returns only one value. How can I change this code to return all columns (3rd row) if a condition is satisfied for both columns? Thank you.

Upvotes: 0

Views: 295

Answers (1)

peterm
peterm

Reputation: 92795

Are you looking for something like this?

UPDATE table1
   SET con = CONCAT(CASE WHEN A < 3 THEN ' A ' ELSE '' END, 
                    CASE WHEN B < 3 THEN ' B ' ELSE '' END);

Here is a SQLFiddle demo

A bit more succinct version that keeps NULLS (if you need it)

UPDATE table1
   SET con = NULLIF(CONCAT(IF(A < 3, ' A ', ''), IF(B < 3, ' B ', '')), '');

Here is a SQLFiddle demo

Upvotes: 2

Related Questions