Forward-only update a lot of rows based on row content?
Posted by: Idan Gazit
Date: January 23, 2007 04:14AM

Hi There,

I am sure that there is some best-practice pattern for doing this, but I could use some assistance in figuring it out.

I am writing a small application to migrate a table from an old schema to a new one. In the new schema there is an additional column whose value is dependent on the other data in the row. I do not need information from any other rows to be able to perform the update, but unfortunately it isn't something that MySQL can calculate for me.

My goal is to find an efficient way to iterate through all of the rows returned by a select statement and update each row as I go, aka "forward-only" read & update. The size of the data makes using a DataSet impractical. The logic step is only dependent on the current row's data, so a forward-only method is best.

My current approach uses a MySqlDataReader on one connection and issues a prepared UPDATE LOW_PRIORITY statement on another connection (I am aware that I cannot do anything on the connection with the open reader). The updates are guaranteed to be updating only rows the reader has already read.

SQL statements look like this (both prepared stmts):

SELECT sequence, foo, bar, baz FROM mytable WHERE category=?category ORDER BY sequence;
UPDATE mytable SET documentid=?documentid WHERE category=?category AND sequence=?sequence;

Program logic looks like this (try/catches omitted for clarity):

while (reader.Read()) {

sequence = reader.GetInt64(0);
foo = reader.GetInt64(1);
bar = reader.GetString(2);
baz = reader.GetDateTime(3);

/*
* Do some computation based on these values here.
*/

// Now write back to the row
// cmdPutDocumentId.conn != reader's underlying connection
this.cmdPutDocumentId.Parameters["?category"].Value = category;
this.cmdPutDocumentId.Parameters["?sequence"].Value = seq;
this.cmdPutDocumentId.Parameters["?documentid"].Value = docid;
this.cmdPutDocumentId.ExecuteNonQuery();

}

Is my approach correct? It seems that this would be a common enough operation that some idiom/pattern would have emerged, but it escapes me.

Thanks,

Idan

Options: ReplyQuote


Subject
Written By
Posted
Forward-only update a lot of rows based on row content?
January 23, 2007 04:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.