Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Jhon Parker
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • 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?

 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • 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
  • 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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic