如何使用同一表中的其他两个列的连接来更新列 [英] 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) GENERATED ALWAYS AS (column_a||'_'||column_b) VIRTUAL

注意:对其使用有某些限制.因此，在实施之前，请先参考文档.但是，对于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屋！