How to update a column with the concatenation of two other columns in the same table

Question

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

before update AB c ------------- 1 4 2 5 3 6 after update AB c ------------- 1 4 1_4 2 5 2_5 3 6 3_6 

How can i do this in oracle?

Answer 1

Firstly, you are breaking normalization rules. You should think about design. If you have values ​​in the columns of the table, then to get the calculated value, all you need is a select statement to get the result the way you want. Storing computed values ​​is usually a bad idea and is considered a bad design.

Anyway,

Since you are on 11g , if you really want to have a computed column, I would suggest VIRTUAL COLUMN than manually update the column. There is a lot of overhead associated with the UPDATE statement . Using a virtual column will reduce the amount of overhead. In addition, you will completely get rid of manual effort and these lines of code to update. Oracle does the job for you.

Of course, you will use the same concatenation condition in a 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. Therefore, before implementing it, refer to the documentation. However, for the simple use case provided by the OP, the virtual column is straightforward.

Update . I did a little test. There were few observations. Please read this question for a better understanding of how to implement my proposal.

Answer 2

Use the concatentation operator || :

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

Or better to avoid re-starting if rows are inserted or updated:

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