Append characters to a string in multiple rows in Oracle SQL

Question

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

Answer 1

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...

Answer 2

We could use CONCAT function in Oracle SQL to tackle the issue and it seems cleaner.

UPDATE integration.ol_orders 
SET order_id = CONCAT (order_id,'999') 
WHERE status = 2;

Just for information:

There are two ways to concatenate Strings in Oracle SQL. Either using CONCAT function or || operator.

CONCAT function allows you to concatenate two strings together

SELECT CONCAT( string1, string2 ) FROM dual;

Since CONCAT function will only allow you to concatenate two values together. If you want to concatenate more values than two, you can nest multiple CONCAT function calls.

SELECT CONCAT(CONCAT('A', 'B'),'C') FROM dual;

An alternative to using the CONCAT function would be to use the || operator

SELECT 'My Name' || 'My Age' FROM dual;