CODEWITHSUNDEEP
mysqlsqlif-statementsql-update
Dr. Atul Tiwari
Dr. Atul Tiwari

Reputation: 1085

How to update column text value with the output of multiple if statement in mysql?

For e.g., my table structure is like -

id (int, auto_increment),
value_1_isChecked (int),
value_1 (text),
value_2_isChecked (int),
value_2 (text),
value_3_isChecked (int),
value_3 (text),
extra_info (text),
interpretation (text)

All the values (value_1, value_2, value_3), their checked status (in the form of 0 or 1), extra info is present in database (30k rows). What I am trying is to update concatenated text in interpretation cell which would like this (I know this is not the correct mySql format, I am writing it just for better understanding of my question) -

variable result='<b>Selected Values :-</b><br>';
If(value_1_isChecked = 1) Then
result = result + "<b>[A]. </b>" + value_1 + "<br>";
End If;
If(value_2_isChecked = 1) Then
result = result + "<b>[B]. </b>" + value_2 + "<br>";
End If;
If(value_3_isChecked = 1) Then
result = result + "<b>[C]. </b>" + value_3 + "<br>";
End If;
If(extra_info Is NOT NULL) Then
result = result + "<br><b>Extra info :-</b><br>" + extra_info;
End If;
Update myTable set interpretation = result;

I can't use ELSEIF because multiple values may have their checked status as true (1).

I know, how to achieve this in PHP, but it would take very long time to update thousands (approx. 30K) of rows, that's why I am trying to do it from mySql itself. But I am unable to form a proper query.

Upvotes: 1

Views: 110

Answers (2)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

You can use case statement like this:

UPDATE myTable
SET interpretation = CASE WHEN value_1_isChecked = 1 THEN concat('<b>','value_1','</b>')
                          WHEN value_2_isChecked = 1 THEN concat('<b>','value_2','</b>')
                          WHEN value_2_isChecked = 1 THEN concat('<b>','value_3','</b>')
                          WHEN extra_info Is NOT NULL THEN concat('<b>','extra_info','</b>')
                          ELSE ''
END;

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

Hope this example help you

SQL FIDDLE DEMO

select 
     concat (
           case v1 when '1' then concat('<b>','v1','</b>') else '' end,
           case v2 when '1' then concat('<b>','v2','</b>') else '' end,
           case v3 when '1' then concat('<b>','v3','</b>') else '' end
       )
from table1

Just update the query to match your need and then rewrite as an UPDATE

Upvotes: 1

Related Questions