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

[Jhon Parker]

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

for (int i = 1; i<=list.size; i++)
{
     UPDATE column1, column2
     SET column1='some value', column2='another value'
      where primary_key_table = i;

coomit
} end for

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]

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:
UPDATE column1, column2 SET column1='some value', column2='another value' where primary_key_table = in (select primary_key_value from some_table where process=1)
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]

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:
UPDATE column1, column2 SET column1='some value', column2='another value' where primary_key_table = in (select primary_key_value from some_table where process=1)
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

create type array_ints as table of number; /

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

int[] mySetedArray = null;

 for (int i = 0; i < a_list_with_values.size() ; i++) {
            mySetedArray[i] = a_list_with_values.get(i);
        }
String sp= "{ call set_status(?) }";
            connection = jdbcTemplate.getDataSource().getConnection();

            CallableStatement callableStatement = connection.prepareCall(sp);

            ArrayDescriptor des = ArrayDescriptor.createDescriptor("MySchema.array_ints", connection);
            ARRAY array_of_ids= new ARRAY(des, connection, mySetedArray);
            callableStatement.setArray(1, array_of_ids);

And my stored procedure

CREATE OR REPLACE PROCEDURE set_status ( array_of_ids IN array_ints, ) IS BEGIN update my_table set status= 'no' where id_my_table in (array_of_ids); COMMIT; END; /

this give me this error: inconsitent datatypes: expected number got MySchema.array_ints