(Fwd) How to loop through a database, row by row, and select and update one row at a time

----- Forwarded message from Troy Mulder <[email protected]> -----

Date: Mon, 12 Apr 2010 17:48:37 -0400
From: Troy Mulder <[email protected]>
To: [email protected]
Subject: How to loop through a database, row by row, and select and update
	one  row at a time

   Hello Tim (is it Dr. Bunce?),

   My name is Troy Mulder, and I am trying to get a perl script to interface with a PostgreSQL database. I
   am trying to step through each row of the database, and read one column of the row, and update another
   column of the row.

   When I follow the online tutorial and use the $sth = $dbh->fetchrow_array() method in a while condition,
   as follows:

   while ( @xml_content = $sth->fetchrow_array() ) {

   I am able to select the two columns of interest. And I can do this for LIMIT 10 rows with no problem,
   just using the select command as in:
   while ( @xml_content = $sth->fetchrow_array() ) {
   $sth = $dbh->prepare("SELECT msgid, xmlcontent FROM messages WHERE msgid = 1892362");
   print "Message ID = $msgid\n";
   $sth->execute();
   }

   However, when I put any sort of an update command after that, as in:

   while ( @xml_content = $sth->fetchrow_array() ) {
   $sth = $dbh->prepare("SELECT msgid, xmlcontent FROM messages WHERE msgid = 1892362");
   print "Message ID = $msgid\n";
   $sth->execute();

   $update_cmd = "UPDATE messages SET alteredcontent = '$alteredmsg' WHERE msgid = $msg_id";
   $sth = $dbh->do($update_cmd);
   }

   Suddenly it no longer works after reading the first row. It doesn't go to the next line and continue
   selecting and updating in the while loop until all rows are updated.

   Can you please help me, and tell me what I am doing wrong?

   Respectfully,

   -Troy

----- End forwarded message -----