Add values from Col 5 to values in col 6 and put them into col 7 in the SAME oracle Table

Hi experts,

I have a table Alpha, it has col_1, Col_2, Col_3, Col_4, Col_5, Col_6, Col_7. The datatype in each column is number (10).

I want to add values in Col_5 with values in Col_6 and store the result in col_7 for each row in the table, taking into account NULL values in either column (Col_5, Col_6).  We are adding numbers only. No data conversion. It could be a an update statement or Merge statement.

I tried this, but  did not work.

Update Alpha set Col_7 = ((Select Case when col_5 = -1 or col_6 = -1 then -1
                                                               else  col_5 + col_6
                                                       End)
                                             From Alpha);

Please help.

Thanks
Kamal AgnihotriAsked:
Who is Participating?
 
awking00Connect With a Mentor Commented:
Since you indicated col_5 or col_6 might contain nulls, you may want to modify johnsone's update to this
UPDATE alpha
SET    col_7 = CASE
                 WHEN col_5 = -1
                       OR col_6 = -1 THEN -1
                 ELSE nvl(col_5,0 + nvl(col_6,0)
               END;
 You might also use decode in this instance
update alpha set col_7 = decode(col_5,-1,-1,col_6,-1,-1, nvl(col_5,0) + nvl(col_6,0))
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
update Alpha set .... From Alpha

I suppose you're getting an error?!
0
 
Kamal AgnihotriAuthor Commented:
Alex, Yes I am getting an error. What is the solution.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
johnsoneSenior Oracle DBACommented:
I would guess this is what you are looking for:
UPDATE alpha 
SET    col_7 = CASE 
                 WHEN col_5 = -1 
                       OR col_6 = -1 THEN -1 
                 ELSE col_5 + col_6 
               END; 

Open in new window

Not sure why you have a sub-query.
0
 
johnsoneSenior Oracle DBACommented:
I was assuming that the original was correct, just needed the syntax fixed.
0
 
Geert GOracle dbaCommented:
what ? really ?

update alpha
set col7 = nvl(col5, 0) + nvl(col6, 0)

i guess I spoiled your homework now
1
 
Kamal AgnihotriAuthor Commented:
Thanks. That worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.