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

Please help.

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)
 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))
Alexander Eßer [Alex140181]Software DeveloperCommented:
update Alpha set .... From Alpha

I suppose you're getting an error?!
KamalAgnihotriAuthor Commented:
Alex, Yes I am getting an error. What is the solution.
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 

Open in new window

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

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

i guess I spoiled your homework now
KamalAgnihotriAuthor Commented:
Thanks. That worked.
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.