CODEWITHSUNDEEP
mysqlif-statementconditional-statementsvirtualcreate-table
Youssef
Youssef

Reputation: 21

how to create VIRTUAL column with an if Query in MYSQL

how can i create a virtual cloumn with conditions i have a table with columns a b c

c should be virtual with conditions =>

 1- if 'b' < 16.5 the value  should be "1"
 2- if 'b' BETWEEN 16.5 AND 18.5 the value should be "2"
 3- if 'b' = 25 the value should be "3"

Upvotes: 2

Views: 1842

Answers (2)

Rohan Gupta
Rohan Gupta

Reputation: 41

Algorithm

STEP 1 - First of all create a table.

CREATE TABLE table_name(
    a float,
    b float,
    c float
);

STEP 2 - If b < 16.5 then update value in c column.

UPDATE table_name SET c = 1 WHERE b < 16.5;

STEP 3 - If b between 16.5 and 18.5 then update value in c column.

UPDATE table_name SET c = 2 WHERE b >= 16.5 AND b <= 18.5;

OR

UPDATE table_name SET c = 2 WHERE b BETWEEN 16.5 AND 18.5;

STEP 4 - If b = 25 then update value in c column.

UPDATE table_name SET c = 3 WHERE b = 25;

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133380

You could use a case when 

 select a, b, case when b <16.5 then 1 
                   when b between 16.5 and 18.5 then 2 
                   when b = 25 then 3  
                  end  c
  from my_table

You should also manage the others case with else 

 select a, b, case when b <16.5 then 1 
                   when b between 16.5 and 18.5 then 2 
                   when b = 25 then 3  
                   else 0
                  end  c
  from my_table

Upvotes: 2

Related Questions