Home » SQL & PL/SQL » SQL & PL/SQL » How to Update multiple rows dynamiccally
icon7.gif  How to Update multiple rows dynamiccally [message #229296] Fri, 06 April 2007 09:35 Go to next message
Sathya kasithangam
Messages: 6
Registered: April 2007
Junior Member
Hi all
My query is:
"update ct_warranty set status='C' where warranty_id in (?)"

I set values dynamically to warranty_id holder as 42,43

I got SQL exception as "Invalid Number"

Because the query looks like below after setting the values:

"update ct_warranty set status='C' where warranty_id in ('42,43')"

IF i try to modify the query as
"update ct_warranty set status='C' where warranty_id in to_number('42,43')"
Will the query work?

Or is there any best way to overcome this problem
Please help me

Re: How to Update multiple rows dynamiccally [message #229297 is a reply to message #229296] Fri, 06 April 2007 09:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
('42,43') is a string with only 1 value, not two values.
Re: How to Update multiple rows dynamiccally [message #229299 is a reply to message #229297] Fri, 06 April 2007 09:40 Go to previous messageGo to next message
Sathya kasithangam
Messages: 6
Registered: April 2007
Junior Member
Yes.I got the problem. I need a solution to make my query recognize it as two numbers
Re: How to Update multiple rows dynamiccally [message #229308 is a reply to message #229299] Fri, 06 April 2007 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

All in the thread varying elements in IN list on AskTom.

Regards
Michel
icon4.gif  Re: How to Update multiple rows dynamiccally [message #229314 is a reply to message #229308] Fri, 06 April 2007 10:46 Go to previous messageGo to next message
Sathya kasithangam
Messages: 6
Registered: April 2007
Junior Member
Thanks.
Razz
I have planned to parse my string into tokens using stringtokenizer in java as my application doesn't use procedures.
I will update separately as updating multiple rows using 'in' clause is always a costly process.



Re: How to Update multiple rows dynamiccally [message #229327 is a reply to message #229296] Fri, 06 April 2007 11:50 Go to previous messageGo to next message
dba_bng
Messages: 13
Registered: April 2007
Junior Member
Try this....


update ct_warranty set status='C' where warranty_id in to_number('42,43','99,99')

Hope it works...
Re: How to Update multiple rows dynamiccally [message #229332 is a reply to message #229327] Fri, 06 April 2007 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

42,43 is a list of 2 numbers 42 and 43 and not the number 42.43.
Moreover your syntax is invalid.

Regards
Michel
Re: How to Update multiple rows dynamiccally [message #229335 is a reply to message #229332] Fri, 06 April 2007 12:08 Go to previous messageGo to next message
dba_bng
Messages: 13
Registered: April 2007
Junior Member
Can you please tell me where the syntax is invalid?

Here is a sample...

SQL> select to_number('42,43','99,99') from dual;

TO_NUMBER('42,43','99,99')
--------------------------
4243
Re: How to Update multiple rows dynamiccally [message #229337 is a reply to message #229335] Fri, 06 April 2007 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But you don't have TWO numbers 42 and 43, you only have ONE number 4243.

The OP said:
Quote:
I need a solution to make my query recognize it as two numbers


Regards
Michel
Re: How to Update multiple rows dynamiccally [message #229413 is a reply to message #229296] Sat, 07 April 2007 11:24 Go to previous messageGo to next message
Sathya kasithangam
Messages: 6
Registered: April 2007
Junior Member
yeah. It is not possible to use to_number() also.
I have drawn a conclusion that we can not do such update.
We have to use procedure to separate tokens or other ways to separate the elements in 'IN' list.

We cannot write a single query to complete the update operation
Re: How to Update multiple rows dynamiccally [message #229416 is a reply to message #229413] Sat, 07 April 2007 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you can do it in SQL.
Try this one:
SQL> var txt varchar2(100)
SQL> exec :txt := '42,43'

PL/SQL procedure successfully completed.

SQL> update ct_warranty set status='C' 
  2  where warranty_id in (
  3  with data as (
  4    select substr (txt,
  5                   instr (txt, ',', 1, level  ) + 1,
  6                   instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
  7             as token
  8      from (select ','||:txt||',' txt from dual)
  9    connect by level <= length(:txt)-length(replace(:txt,',',''))+1
 10    )
 11  select * from data )
 12  /

0 rows updated.

Regards
Michel
Re: How to Update multiple rows dynamiccally [message #229425 is a reply to message #229296] Sat, 07 April 2007 12:50 Go to previous messageGo to next message
Sathya kasithangam
Messages: 6
Registered: April 2007
Junior Member
This query looks great Cool
I can test this query on monday only.
I am worried about the processing speed of the query.

I am not familiar with SQL Queries.
So, could you please explain me "Connect by level","Select * from data" and also how the query works overall apart from separating token functionality?
Re: How to Update multiple rows dynamiccally [message #229428 is a reply to message #229425] Sat, 07 April 2007 13:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I didn't need the "data" level. It can be done directly with:
SQL> update ct_warranty set status='C' 
  2  where warranty_id in (
  3    select substr (txt,
  4                   instr (txt, ',', 1, level  ) + 1,
  5                   instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
  6             as token
  7      from (select ','||:txt||',' txt from dual)
  8    connect by level <= length(:txt)-length(replace(:txt,',',''))+1
  9   )
 10  /

0 rows updated.

The "connect by" used here is just a line generator:
SQL> select level from dual connect by level <= 5;
LEVEL
-----
    1
    2
    3
    4
    5

5 rows selected.

The expression "length(:txt)-length(replace(:txt,',',''))+1" just count how many elements there are in the list.
As you saw it, the inner query just separate the elements of your list: each "level" (each line of the generator) pick one element using substr and instr.
SQL>    select substr (txt,
  2                    instr (txt, ',', 1, level  ) + 1,
  3                    instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
  4              as token
  5       from (select ','||:txt||',' txt from dual)
  6     connect by level <= length(:txt)-length(replace(:txt,',',''))+1
  7  /
TOKEN
-------------------------------------------------------------
42
43

2 rows selected.

Regards
Michel

[just change a word for another one and switch 2 lines]

[Updated on: Sat, 07 April 2007 13:38]

Report message to a moderator

Re: How to Update multiple rows dynamiccally [message #229430 is a reply to message #229296] Sat, 07 April 2007 13:08 Go to previous message
Sathya kasithangam
Messages: 6
Registered: April 2007
Junior Member
THANK YOU MICHEL Cool

If this works fine, i will give you treat Razz
Previous Topic: Search for value withing hierarchy paths
Next Topic: exists
Goto Forum:
  


Current Time: Sun Dec 04 20:26:38 CST 2016

Total time taken to generate the page: 0.12117 seconds