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

Execute your subquery (SELECT order_id FROM integration.ol_orders WHERE status = 2) and see how many rows are returned. — mmmmmpie
– mmmmmpie, Commented Feb 6, 2015 at 18:10
Theres your problem. You are trying to set 1 row equal to the output of 14 rows + '999'. BTW + isn't how you concatenate in oracle its ||. — mmmmmpie
– mmmmmpie, Commented Feb 6, 2015 at 18:15
@mmmmmpie how would I be able to adjust that "set 1 row equal to" to "set our sub-queries values to our updated values"? — antihero989
– antihero989, Commented Feb 6, 2015 at 18:20
I don't really understand the question but what you are trying is really well explained in @jpw's answer below. — mmmmmpie
– mmmmmpie, Commented Feb 6, 2015 at 18:21

jpw · Accepted Answer · 2015-02-06 18:24:20Z

5

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

edited Feb 6, 2015 at 18:24

answered Feb 6, 2015 at 18:13

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Patrick Bacon Over a year ago

I agree, though it would be nice to confirm that order_id is not a number. Certainly antihero989 does treat it that way in the attempt above.

Allan Over a year ago

Oracle will implicitly convert order_id to a varchar2 in order to apply the concatenation.

Patrick Bacon Over a year ago

True, though I dislike depending on implicit casting.

jpw Over a year ago

@Allan Thanks, wasn't sure about that.

Du-Lacoste · Accepted Answer · 2022-04-15 07:09:02Z

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;

Collectives™ on Stack Overflow

Append characters to a string in multiple rows in Oracle SQL

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related