Thread: update by one transaction

BEGIN isn't the solution, just I forgot to write it. So I repeat my
problem again:

I have a table for instance boci (row_number int, col2 float, col3
float). The boci table has about 10000 rows.
I want to update col3 column of the table in every row with different
values. Desired values are in an array has 10000 elements. I want to be
the value of the col3 column in the first row equal to the first element
of the array and so on.
I've done it with a 'for' cycle, but it is very slow. I want to do
theese updates with one transaction.

Here is a little piece of my program:

PQexec(conn, "BEGIN");
for (i=0; i<10000; i++)
{
    sprintf(buff, "UPDATE boci SET col3 = %f WHERE row_number=%d",
array[i], i);
    PQexec(conn, buff);
}
PQexec(conn, "END");

This program is very, very slow. Is there any way making this program
much faster (for instance with CURSOR or 'block write' or something
else)? Please write me a little program that describes your ideas!

Thanks for your help in advance!

Please help me!!!

 =>From: Lendvary Gyorgy <gyurika@prolan.hu>
 =>...
 =>PQexec(conn, "BEGIN");
 =>for (i=0; i<10000; i++)
 =>{
 =>    sprintf(buff, "UPDATE boci SET col3 = %f WHERE row_number=%d",
 =>array[i], i);
 =>    PQexec(conn, buff);
 =>}
 =>PQexec(conn, "END");
 =>
 =>This program is very, very slow.

The first thing I'd check is that you have an index on
boci(row_number).  If you do, or if adding one doesn't help, then
perhaps you could split boci into two tables:

        boci1( row_number int, col2 float )
        boci2( row_number int, col3 float )

Then you could use the COPY command to load data from your array.  To
select things, just join boci1 and boci2 on row_number (or create a
view, if PostgreSQL supports 'em).

d.