Can't you use the replace sql query? Use it like you use "insert". It will insert new rows where there are no rows, and do an update where there are rows...
Teddy ----- Original Message ----- From: "Peter Rabbitson" <[EMAIL PROTECTED]> To: <beginners@perl.org> Sent: Sunday, July 10, 2005 18:09 PM Subject: DBI insert vs update question > Hello everyone, > I want someone to share his expertise on the following: > Suppose we have a table with some data that periodically must be synced > with an external source, which provides only partial information (e.g. > it might provide all the fields for a new record, but might provide > only a single field difference for an already existing record). This > obviously will involve a series of UPDATE and INSERT statements, mostly > in random order. As most RDBMS can hold only one prepared statement at a > time (including MySQL) I have 2 ways to do this that look somewhat > efficient: > > 1. Sweep the pre-existing table into a hash, DELETE all the records from > it and INSERT the new data, using the hash as a source for missing > fields. > > 2. Create 3 connections to the database, have the first one hold a > SELECT statement that will replace the hash above, have the second > connection be an INSERT for nonexisting records and the third connection > be an UPDATE for existing ones > > >From reading on DBI I understood that moving lots of data with do() > statements is pretty slow (e.g. constantly re-preparing the same > statement), so I think scenario 2 with a single connection is even > worse. > > The reason I am elaborating on this is that I am afraid the dataset will > stop fitting in memory at some point of time if I go with scenario 1 > (which is easier and cleaner to implement). Also I have no idea how > resource intensive scenario 2 would be, although I have proper > indexing and stuff so the UPDATES should be fast enough... > > Thanks > > Peter > > -- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > <http://learn.perl.org/> <http://learn.perl.org/first-response> > > -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] <http://learn.perl.org/> <http://learn.perl.org/first-response>
