How to pass multiple values to UPDATE or SELECT COUNT(*) statement

Question

I am trying to do a update a record.

Table name is customer

id | name    | address         | state
---+---------+------------+-------------------------
 1 |    John | 123 main st     | TX
 2 |    Jack | 678 John st     | NJ
 3 |    Bet  | 987 Tx st        | NY
 4 |    Maddy| 9812 Hudson st | CA
 5 |    ABCD | 9813 Mainly st  | PA

My query is like below

UPDATE CUSTOMER c SET c.state = 'CA' WHERE c.id IN (idList);

Where idList is a localVariable that I created and it returns a list of id like 1,3,5

The query is working if do it like

UPDATE CUSTOMER c SET c.state = 'CA' WHERE c.id IN (1,3,5);

It is updating the respective records to CA.

But if I use it as

UPDATE CUSTOMER c SET c.state = 'CA' WHERE c.id IN (idList);

I get the below error. I don't want to pass the list directly as the list might change. I am getting the list of ids using a different command where it returns and assigns to idList as 1,3,5

Error:

ORA-01722: invalid number
ORA-06512: at line 35
01722. 00000 - "invalid number"**

How to solve this? I am writing it as a stored procedure.

Would look like this as a example ``` Select s.ID_LIST into idList from CUSTOMERDETAILS.ID s where s.FILTER_STATE = ‘CA_STATE’; ``` O/P would be like ``` ID_LIST 1,3,5 ``` — user11614685, Dec 24 '19 at 17:39
If I do DBMS_OUTPUT.PUT_LINE('IDList values' || idList); I am able to see the list that is generated 1,3,5 . But not sure what is going wrong if I pass idList in query it is not working but working if I pass the numbers directly — user11614685, Dec 24 '19 at 17:43
You can either use TYPE or WITH clause. Please take a look at this link : https://stackoverflow.com/questions/35231757/pl-sql-use-list-variable-in-where-in-clause. Hope that can help you. — Mohamad TAGHLOBI, Dec 24 '19 at 17:52
If the column in the table is also a string and stores a comma separated list of IDs, you really should read [this](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) and fix that schema. — sticky bit, Dec 24 '19 at 18:00

score 1 · Answer 1 · answered Dec 24 '19 at 17:58

I guess idList is a comma separated string with all the ids that you want to update.
So what is happening is that the operator IN compares each id with that string and since this comparison can't succeed in any case you get an error.
What you can do instead is use the LIKE operator:

UPDATE customer
SET "state" = 'CA'
WHERE ',' || idList || ',' LIKE '%,' || "id" || ',%'

See the demo.

score 0 · Answer 2 · answered Dec 24 '19 at 17:48

0

You may create parameterized query string and pass values like,

UPDATE CUSTOMER c SET c.state = 'CA' WHERE c.id IN (:idList);

answered Dec 24 '19 at 17:48

Vivek

376
1
14

This doesn't work for multiple values. As the data for IdList will be huge don't wanna pass one num at a time for 1 run. – user11614685 Dec 24 '19 at 17:53
I thought you are passing values from java. Read all ids using cursor and loop over it and use single value for update query. Run the procedure it will update all ids. – Vivek Dec 24 '19 at 18:21

score 0 · Answer 3 · answered Dec 24 '19 at 21:01

0

You can't substitute a text variable for a list of values - it's simply not allowed. You're going to have to use dynamic SQL:

EXECUTE IMMEDIATE 'UPDATE CUSTOMER c SET c.state = ''CA'' WHERE c.id IN (' || idList || ')';

answered Dec 24 '19 at 21:01

Bob Jarvis - Слава Україні

48,992
9
77
110

How to pass multiple values to UPDATE or SELECT COUNT(*) statement

3 Answers3