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

Question

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);

Ed Bangga · Accepted Answer · 2019-10-17 03:06:20Z

2

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

edited Oct 17, 2019 at 3:06

answered Oct 17, 2019 at 2:45

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ed Bangga Over a year ago

@Tri, coalesce() is sql standard, so it can work to mysql, postgres.....

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related