CODEWITHSUNDEEP
sql
Tri
Tri

Reputation: 35

how to update a row with another row if column values are null in SQL

i have 4 column, Sup1,Sup2,Sup3 and Vers.

i have to update Vers values from sup1, if sup1 null get values from sup2, if sup 2 null get values from sup3. if Sup1,Sup2,Sup3 are null then NULL.

look like this :

sup1 sup2 sup3 vers
1               1
      2         2
           3    3
4     4    5    5
0     0    0   NULL

i have tried this 

 update CRATEST set  
 --more next ( 4 to n ) columns here if required       
 Vers = (case when (Sup1 is null) then Sup2  
         when (Sup2 is null) then sup3 
         when (sup3 is null) then Sup1 else 'NULL' end);

Upvotes: 1

Views: 46

Answers (1)

Ed Bangga
Ed Bangga

Reputation: 13006

you can use coalesce() function

update CRATEST set 
Vers =  case when (coalesce(Sup1, 0) + coalesce(Sup2, 0) + coalesce(Sup3, 0)) = 0 then NULL
    else coalesce(Sup3, Sup2, Sup1) end

Upvotes: 2

Related Questions