CODEWITHSUNDEEP
mysqlsql
Lim Neo
Lim Neo

Reputation: 75

SQL update multiple rows with condition

Name   id  Col1  Col2  Col3 
Row1   1    6     1     A    
Row2   2    2     3     B    
Row3   3    9     5     B    
Row4   4    16    8     C

I want to update the column with condition.

In first row,

update col2 = Col1+0 if Col3 = A 
OR 
col2 = Col1-0 if Col3 = B 
OR 
col2 = Col1*0 if Col3 = C

In second row,

update col2 = (previous col2) + Col1 if Col3 = A 
OR
col2 = (previous col2) - Col1 if Col3 = B 
OR
col2 = (previous col2) * Col1 if Col3 = C

The same goes to third row

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

I think you can do what you want with a case statement and variables:

set @prev_col2 = 0;

update t
    set col2 = (case when (@col2 := @prev_col2) = NULL then -1 -- never happens
                     when (@prev_col2 := col2) = NULL then -1  -- never happens
                     when col3 = 'A' then Col1 + @col2
                     when col3 = 'B' then Col1 - @col2
                     when Col3 = 'C' then col1 * @col2
                end)
    order by id;

Upvotes: 1

Related Questions