How to update a column with concatenate of two other column in a same table

Question

I have a table with 3 columns a, b and c. I want to know how to update the value of third column with concatenate of two other columns in each row.

before update
 A    B    c 
-------------
1     4
2     5
3     6

after update
 A    B    c 
-------------
1     4    1_4
2     5    2_5
3     6    3_6

How can I do this in oracle?

Answer 1

Use the concatentation operator ||:

update mytable set
c = a || '_' || b

Or better, to avoid having to rerun this whenever rows are inserted or updated:

create view myview as
select *, a || '_' || b as c
from mytable

Answer 2

Firstly, you are violating the rules of normalization. You must re-think about the design. If you have the values in the table columns, then to get a computed value, all you need is a select statement to fetch the result the way you want. Storing computed values is generally a bad idea and considered a bad design.

Anyway,

Since you are on 11g, If you really want to have a computed column, then I would suggest a VIRTUAL COLUMN than manually updating the column. There is a lot of overhead involved with an UPDATE statement. Using a virtual column would reduce a lot of the overhead. Also, you would completely get rid of the manual effort and those lines of code to do the update. Oracle does the job for you.

Of course, you will use the same condition of concatenation in the virtual column clause.

Something like,

Column_c varchar2(50) GENERATED ALWAYS AS (column_a||'_'||column_b) VIRTUAL

Note : There are certain restrictions on its use. So please refer the documentation before implementing it. However, for the simple use case provided by OP, a virtual column is a straight fit.

Update I did a small test. There were few observations. Please read this question for a better understanding about how to implement my suggestion.