CODEWITHSUNDEEP
phpmysqlcodeigniter
Praveen Kumar
Praveen Kumar

Reputation: 2408

Create and update a virtual column value in mysql query

My table structure is 

customer  column1 column2(int) 
  1        sale      2            
  1        return    1            
  1        sale      3             
  1        sale      1             
  1        return    1

Question:-

create a virtual column(column3) which has start value `column3 = 0` and update its value according to `column1` and `column2` values. I could not find a query which create and update a virtual column together.

e.g. IF(column1='sale',column3 + column2, column3-column2 )

// start value of column3 = 0 for each product
customer  column1 column2(int) column3
  1        sale      2             2  //0+2 because it is a sale 
  1        return    1             1  //2-1 because it is a return 
  1        sale      3             4  //1+3 because it is a sale 
  1        sale      1             6  //4+2 because it is a sale 
  1        return     1            4  //6-2 because it is a return

Upvotes: 0

Views: 475

Answers (1)

mitkosoft
mitkosoft

Reputation: 5316

CROSS JOIN should solve this:

SELECT
    t.column1,
    t.column2,
    CASE
WHEN t.column1 = 'sale' THEN
    @id :=@id + t.column2
ELSE
    @id :=@id - t.column2
END AS column3
FROM
(SELECT @id:=0) var
CROSS JOIN table1 t

Upvotes: 1

Related Questions