如何更新与同一个表中其他两个列的连接的列 [英] How to update a column with concatenate of two other column in a same table

问题描述

我有一个包含 3 列 a、b 和 c 的表.我想知道如何用每行中其他两列的连接来更新第三列的值.

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

我如何在 oracle 中做到这一点?

How can I do this in oracle?

推荐答案

首先，你违反了规范化的规则.您必须重新考虑设计.如果您在表列中有值，那么要获得计算值，您只需要一个 select 语句以您想要的方式获取结果.存储计算值通常是一个坏主意，被认为是一个糟糕的设计.

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.

不管怎样，

由于您使用的是 11g，如果您真的想要一个计算列，那么我建议您使用 VIRTUAL COLUMN 而不是手动更新该列.UPDATE 语句涉及很多开销.使用虚拟列会减少很多开销.此外，您将完全摆脱手动工作和执行更新的那些代码行.Oracle 为您完成这项工作.

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.

类似的，

Column_c varchar2(50) 始终作为 (column_a||'_'||column_b) 虚拟生成

注意:对其使用有一定的限制.因此，请在实施之前参考文档.但是，对于 OP 提供的简单用例，虚拟列是很合适的.

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.

这篇关于如何更新与同一个表中其他两个列的连接的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！