Re: Superior way to update SQL tables

First off, have you correctly defined primary and unique keys? If you haven't, this is going to be extremely difficult.

Second, what database are you using? MySQL has the REPLACE command, which was designed to do what you're asking. Otherwise, you can depend on your primary and unique keys to fail appropriately, allowing you to do something like:

my $dbh = DBI->connect(
    $dsn, $user, $password, {
        PrintError => 0,
        RaiseError => 1,
    },
) or die $DBI::errstr;

foreach (@whatever)
{
    eval {
        $dbh->do( $insert_statement, {}, @values );
    }; if ($@) {
        $dbh->do( $update_statement, {}, @values );
    }
}
[download]

The theory is that if you insert something that isn't there yet, you're fine. If it's there, the primary and unique keys will complain, so you do an update.

------
We are the carpenters and bricklayers of the Information Age.

Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

I shouldn't have to say this, but any code, unless otherwise stated, is untested

Comment on Re: Superior way to update SQL tables Download Code

Replies are listed 'Best First'.
Re^2: Superior way to update SQL tables by radiantmatrix (Parson) on Sep 08, 2004 at 15:28 UTC
The methodology you describe is exactly what I am doing. The idea of attempting the update from within the fail block is interesting, and something I hadn't considered. Thank you! I note that you use `$dbh->do` rather than prepare/execute. Obviously, that is easier, but are there caveats to running several million `do`'s as opposed to one `prepare` and millions of `execute(@values)`? Also, is there a good reason not to do: `foreach (my @row = $sth->fetchrow_array) { eval { $dbh2->do( $insert_statement, {}, @row ); }; if ($@) { eval { $dbh2->do( $update_statement, {}, @row ); }; if ($@) { addto_Skip(@row); } } }` [download] ? ^{-- $me = rand($hacker{perl});}	[reply] [d/l]
Re^3: Superior way to update SQL tables by dragonchild (Archbishop) on Sep 08, 2004 at 15:49 UTC
I used do() for illustrative purposes. I would actually recommend using prepare_cached()/execute() instead of prepare()/execute(). do() has to re-prepare the statement, which can take time. As for your addto_Skip(@row) thing, that's up to you. If you expect to have rows that you cannot insert or update, that would be a useful thing to have. You can also do something like: `while (my @row = $sth->fetchrow_array ) { eval { $sth_insert->execute( @row ) }; next unless $@; eval { $sth_update->execute( @row ) }; next unless $@; addto_Skip( @row ); }` [download] Though, personally, I would look at using fetch() with bind_columns(), as the best performance option that DBI supports. This is instead of the fetchrow_array(). ------ We are the carpenters and bricklayers of the Information Age. Then there are Damian modules.... sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon.* - flyingmoose I shouldn't have to say this, but any code, unless otherwise stated, is untested	[reply] [d/l]