将字符附加到Oracle SQL中多行中的字符串(Append characters to a string in multiple rows in Oracle SQL)

将字符附加到Oracle SQL中多行中的字符串(Append characters to a string in multiple rows in Oracle SQL)

我试图通过附加字符串'999'来更新多行中的单个列：

UPDATE integration.ol_orders SET order_id = ((SELECT order_id FROM integration.ol_orders WHERE status = 2) || '999') WHERE status = 2

但不管出于什么原因，我总是收到“ORA-01427：单行子查询返回多行提示”的错误。

所以，当我迭代我的n行时，我试图做到：

a1 = a1 || '999' a2 = a2 || '999' a3 = a3 || '999' an = an || '999'

有什么建议怎么办？

编辑：将'+'更改为'||'，仍然没有运气

I'm trying to update a single column in multiple rows by appending the string '999':

UPDATE integration.ol_orders SET order_id = ((SELECT order_id FROM integration.ol_orders WHERE status = 2) || '999') WHERE status = 2

but for whatever reason, I keep getting the error of "ORA-01427: single-row subquery returns more than one row tips".

So, as I iterate my n rows, I'm trying to do:

a1 = a1 || '999' a2 = a2 || '999' a3 = a3 || '999' an = an || '999'

Any suggestions how to go about this?

edit: changed '+' to '||', still no luck

最满意答案

子查询看起来没有必要，只要这样做就可以工作：

UPDATE integration.ol_orders SET order_id = order_id || '999' WHERE status = 2

如果我记得Oracle正确使用|| 用于连接。 如果它是一个整数，可能需要将order_id转换为字符类型，我对此不确定，目前无法对其进行测试。 （如注释中所述，转换应该是隐含的，否则可以使用TO_CHAR()明确地TO_CHAR()它）。

确保在运行之前备份或复制表格...

The subquery looks unnecessary, just doing this should work:

UPDATE integration.ol_orders SET order_id = order_id || '999' WHERE status = 2

If I remember right Oracle uses || for concatenation. It might be necessary to cast the order_id to a character type if it's an integer, I'm not sure about that and can't test it at the moment. (The conversion should be implicit as stated in a comment, otherwise you can use TO_CHAR() to cast it explicitly).

Make sure to have a backup or copy of the table before running though...