我试图通过附加字符串'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 = 2but 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 = 2If 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...
更多推荐
发布评论