• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Paul Clapham
  • Ron McLeod
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Roland Mueller
  • Piet Souris
Bartenders:

How can i update multiple rows based on a list of ids

 
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi i need to update some columns from some rows in a table, i'm using a oracle database.

I have a java method that returns a list of primary keys, this primary keys refers the rows that i need to update, my problemen is that i dont know what would be the best choice to updates this rows.

Here is an example:

I tun my method and my method return a list with five primary keys (five ids) like this : (2, 4, 5, 1, 9) then i need to update the rows that represent those primary keys

Bu i dont know what is the best choice to do this. and i have the following ideas

1) have a store procedure with a for method that goes from 1 to the size of the list and inside this for method i would put a update sentences like this



2) my second idea is if there a way to do and update and pass it my list of primary keys in the "where" part of the select, like putting a select in the "where" part and this select would bring me the list of ids,
if this is posible how could it be?

 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
1)
First of all, you need to use PreparedStatement - here is the why.

Then use JDBC batching to set up and execute several statements in one go. Have a look at this example. If you use a recent Oracle JDBC driver, use a PreparedStatement, and use only one statement (repeatedly) in one batch, it would probably perform quite well.

2)
You can use results from one query in another query. In your case, it might be something like this:

If you need to supply the values for the IN clause some other way (say, from Java), you might use the technique described here for batching selects, except that you'd batch inserts. But I'd say that the JDBC batching I've mentioned above is much less complicated and would do the job just fine.
 
Jhon Parker
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Martin Vajsar wrote:1)
First of all, you need to use PreparedStatement - here is the why.

Then use JDBC batching to set up and execute several statements in one go. Have a look at this example. If you use a recent Oracle JDBC driver, use a PreparedStatement, and use only one statement (repeatedly) in one batch, it would probably perform quite well.

2)
You can use results from one query in another query. In your case, it might be something like this:

If you need to supply the values for the IN clause some other way (say, from Java), you might use the technique described here for batching selects, except that you'd batch inserts. But I'd say that the JDBC batching I've mentioned above is much less complicated and would do the job just fine.



thanks for the info this is what i have try

So far i have this:

this is the type i creat ein the database



the java code when i set the array to pass it to the stored procedure



And my stored procedure



this give me this error: inconsitent datatypes: expected number got MySchema.array_ints
 
reply
    Bookmark Topic Watch Topic
  • New Topic