SQL Oracle：通过添加到现有值来更新数据行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26405864/
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
Oracle: Update a datarow by adding to existing value
提问by DatRid
I have a big table in my database(Oracle 11g) where I want to update a column of the table where I have specific numbers in it.
I want to update it with setting a prefixinfront of every value of this column.
我的数据库（Oracle 11g）中有一个大表，我想在其中更新表中包含特定数字的列。我想通过设置prefix此列的每个值的前面来更新它。
Example(SpecificNumber is intand the PK, Value1 is Varchar2and so is the last column):
示例（SpecificNumber 是intPK，Value1 是Varchar2最后一列）：
Actual data:
实际数据：
[SpecificNumber] [Value1] [column2gettingupdated]
[1] 123456789 Test text
[2] 123456789102 sth text2
[3] 12233 text test3
Future data(after my sql):
未来数据（在我的 sql 之后）：
[SpecificNumber] [Value1] [column2gettingupdated]
[1] 123456789 Test PREFIX-text
[2] 123456789102 sth PREFIX-text2
[3] 12233 text PREFIX-test3
So what I thought about:
所以我想到的是：
Update table
set column2gettingupdated=("prefix"+
(select column2gettingupdated from table where SpecificNumber = '12233')
)
where SpecificNumber = '12233';
But that gives me an ORA-00904: "p": invalid identifiererror.
但这给了我一个ORA-00904: "p": invalid identifier错误。
And if I try this:
如果我尝试这个：
Update table
set column2gettingupdated=("prefix"+
(select column2gettingupdated from table where SpecificNumber = '12233')
)
where SpecificNumber = '12233';
I get an ORA-01722: invalid numbererror.
我收到一个ORA-01722: invalid number错误。
Is this even possible ? Already thanks for your help!
这甚至可能吗？已经感谢您的帮助！
回答by Patrick Bacon
All that is needed is to use the concatenation operator, ||. Update syntax does not require that you have a subquery to obtain column2gettingupdatedfor the value, 12233.
所需要的只是使用连接运算符，||。更新语法不要求您有一个子查询来获取column2gettingupdated值 12233。
Also, with Oracle VARCHAR2's, you use single quotes and not double quotes. This results in this syntax for this statement:
此外，对于 Oracle VARCHAR2，您使用单引号而不是双引号。这导致此语句的语法如下：
UPDATE table
SET column2gettingupdated = 'prefix' || column2gettingupdated
WHERE SpecificNumber = 12233;
Here is an example from the example schema SCOTT:
这是示例架构 SCOTT 中的一个示例：
SCOTT@dev> CREATE TABLE DEPT2 as (
2 SELECT *
3 FROM DEPT
4 );
Table created.
SCOTT@dev> commit;
Commit complete.
SCOTT@dev> UPDATE DEPT2
2 SET DNAME = 'PRE '|| DNAME
3 WHERE DEPTNO = 20;
1 row updated.
SCOTT@dev> commit;
Commit complete.
SCOTT@dev> SELECT *
2 FROM dept
3 WHERE deptno = 20
4 UNION
5 SELECT *
6 FROM dept2
7 WHERE deptno = 20
8
SCOTT@dev> /
DEPTNO DNAME LOC
========== ============== =============
20 PRE RESEARCH DALLAS
20 RESEARCH DALLAS
回答by Rusty
Use concatenation for strings:
对字符串使用连接：
update <table_name>
set column2gettingupdated = 'Prefix-' || column2gettingupdated
where specificnumber = 12233; -- use NUMBER literal instead of string one
