oracle 如何通过连接同一表中的其他两列来更新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28542578/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update a column with concatenate of two other column in a same table
提问by jalal rasooly
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.
我有一个包含 3 列 a、b 和 c 的表。我想知道如何通过连接每行中的其他两列来更新第三列的值。
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?
我怎么能在oracle中做到这一点？
采纳答案by Lalit Kumar B
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 selectstatement to fetch the result the way you want. Storing computed values is generally a bad idea and considered a bad design.
首先，您违反了规范化规则。你必须重新考虑设计。如果您有表列中的值，那么要获得计算值，您只需要一个select语句来以您想要的方式获取结果。存储计算值通常是一个坏主意，被认为是一个糟糕的设计。
Anyway,
反正，
Since you are on 11g, If you really want to have a computed column, then I would suggest a VIRTUAL COLUMNthan manually updating the column. There is a lot of overheadinvolved with an UPDATEstatement. 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.
既然你在11g，如果你真的想要一个计算列，那么我会建议一个VIRTUAL COLUMN 而不是手动更新列。UPDATE语句涉及很多开销。使用虚拟列会减少很多开销。此外，您将完全摆脱手动工作和执行更新的那些代码行。Oracle 为您完成这项工作。
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
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.
注意：它的使用有一定的限制。因此，请在实施之前参考文档。但是，对于 OP 提供的简单用例，虚拟列是直接拟合的。
UpdateI did a small test. There were few observations. Please read this questionfor a better understanding about how to implement my suggestion.
更新我做了一个小测试。观察结果很少。请阅读此问题以更好地了解如何实施我的建议。
回答by Bohemian
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
